Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ENHANCE Migrated HINTS where the number of Query Block changes
  with UN/UA.  OPTHINT PACKAGE contains both QueryBlock V7 &
  QueryBlock V8, receiving SQLCODE395 TOKENS 16 & 32.  Original
  or favored access path is not taken in such cases.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 Optimization Hints.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: Various problems may occur while using  *
  *                      Optimization Hints.                     *
  *                      (1) Opthint taken from V7 containing    *
  *                      a top-level UNION or UNION ALL is       *
  *                      not picked up in V8                     *
  *                      (2) Opthint for multi-index access      *
  *                      is not picked up                        *
  *                      (3) ABEND0C4-11 in DSNXOPTH+3F78        *
  *                      may occur intermittently when           *
  *                      binding with an optimization hint       *
  *                      (4) SQL code +395 may occur for         *
  *                      a Sort Merge Join hint even though      *
  *                      it can be taken                         *
  *                      (5) Opthint taken from V7 containing    *
  *                      an INSERT with Subselect and            *
  *                      UNION/UNION ALL or outer join or        *
  *                      table expression is not picked up       *
  *                      in V8                                   *
  *                      (6) Hint with PREFETCH = 'D' gets       *
  *                      SQL code +395 token '8'                 *
  *                      (7) Storage problem because index       *
  *                      plans may not be freed for multi-index  *
  *                      plans                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Various problems may occur while using Optimization Hints.
  (1) An Opthint taken from V7 containing a top-level UNION or
      UNION ALL is not picked up in V8.  The user may see
      SQL code +395 when the hint is not taken.
  (2) An Opthint for multi-index access is not picked up.
      Cost-based analysis may cause a single index access path
      to win out over a multi-index access path.  Or the
      indexes specified by the user are not chosen for
      multi-index access, even though there are no internal
      restrictions on its use.
  (3) ABEND0C4-11 in DSNXOPTH+3F78 may occur intermittently when
      binding with an optimization hint.  Internal storage is
      referenced beyond the end of the allocated block.  An
      abend could occur if it extends into the next page of
      memory.
  (4) SQL code +395 may occur for a Sort Merge Join hint even
      though there are no internal restrictions on its use.
  (5) An Opthint taken from V7 containing an INSERT with Subselect
      is not picked up in V8 if the Subselect contains
      UNION/UNION ALL or an outer join or a table expression.
  (6) An Opthint with PREFETCH = 'D' gets SQL code +395 token '8'.
  (7) A storage problem may occur because index plans may not be
      freed for multi-index plans when a multi-index access hint
      is specified.  This could result in various Storage
      Manager abends or a gradual increase in storage usage.
  
  Additional Keywords:
  OPTHINT  OPTHINTS  SET CURRENT OPTIMIZATION HINT
  SQLUNION  SQLUNIONALL  SQLCODE395  SQLMIDX  MIDX
  MULTIINDEX  ABEND0C4  SQLSTORAGE  SQLSMJ  SQLMSJ  SMJ  MSJ
  SQLINSERT  SQLSUBQUERY  SQLOUTERJOIN  SQLJOIN  SQLTABLEEXPR
  SQLINSWSELECT
  

Problem conclusion

• The various problems listed above are corrected.
  (1) If there are no other internal restrictions on using the
      optimization hint, then a query taken from V7 containing
      a top-level UNION or UNION ALL can now get picked up in V8.
  (2) For an Opthint that contains multi-index access, DB2 will
      now attempt to use the indexes that the user specified
      first.  If that is not possible, the user will get SQL
      code +395 with a new token '40' to indicate that the
      multi-index access hint could not use the indexes
      specified.  The DB2 Codes manual will be updated
      with the new token '40':
  
      40  Multi-index access cannot use the indexes specified.
  
      If no multi-index access can be used, then DB2 will revert
      to a different access type.
  (3) The ABEND0C4 in DSNXOPTH is corrected.
  (4) If there are no other internal restrictions on using the
      optimization hint, then a Sort Merge Join hint can now
      get picked up in V8.
  (5) If there are no other internal restrictions on using the
      optimization hint, then a query taken from V7 containing
      an INSERT with Subselect and either UNION/UNION ALL,
      an outer join, or a table expression, can now get picked
      up in V8.
  (6) If there are no other internal restrictions on using the
      optimization hint, then a hint containing PREFTECH='D'
      can now get picked up in V8.
  (7) Storage is now freed for multi-index access hints
      after hints processing is done, to avoid storage creep
      and storage abends.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK07760

Modules/Macros

• DSNXIDX  DSNXOGCM DSNXOPT  DSNXOPTH DSNXOPTJ
  DSNXOTS
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK07760

     UP05/10/19 P F510

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.

Product categories:

Software

Data Management

Data Servers (Database Management Systems)

DB2 for z/OS

Software version:

810

Reference #:

PK07750

IBM Group:

Software Group

Modified date:

2005-11-03