A fix is available
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 number
PK07750
Reported component name
5740 IBM DATABA
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2005-06-21
Closed date
2005-09-30
Last modified date
2005-11-03
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.
Copyright and trademark information
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.
Rate this page
Please take a moment to complete this form to help us better serve you.