A fix is available
APAR status
Closed as program error.
Error description
Query encounters poor perforamnce becauses multi-index access is not used, multi-index access is not used because the limit of optimizer storage usage is exceeded. For example, a query contains many OR predicates SELECT * FROM T1 WHERE C1 = ? AND ( (C2 = ? AND C3 = ?) OR (C2 = ? AND C3 = ?) OR (C2 = ? AND C3 = ?) ...... OR (C2 = ? AND C3 = ?) ) ; Table T1 has an index I1 on (C1,C2,C3). The optimal access type can be multi-index access using I1 with MATCHCOLS=3. But when optimizer does access path selection, the multi-index access path is discarded because the limit of optimizer storage usage is exceeded. Then single index access with MATCHCOLS=1 will be used.
Local fix
BYPASS/CIRCUMVENTION: Increase hiden zparm for optimizer storage: MAX_OPT_STOR
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users who have a * * complex query with many OR predicates and * * MULTI-ROW FETCH is specified. * **************************************************************** * PROBLEM DESCRIPTION: * * Db2 does not build multi-index plan * * for a complex query including many * * OR predicates, due to the limitation * * of optimizer storage usage. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** Db2 may select an inefficient single index to access the leading table for a complex query which includes many OR predicates, when MULTI-ROW FETCH is specified. Multi-index plan is expected, but Db2 does not choose it. The reason is that Db2 does not build a multi-index plan due to the limitation of optimizer storage usage. Example: SELECT * FROM T1 WHERE C1 = ? AND ( (C1 =? AND C2 = ? AND C3 = ?) OR (C1 =? AND C2 = ? AND C3 = ?) OR (C1 =? AND C2 = ? AND C3 = ?) ...... OR (C1 =? AND C2 = ? AND C3 = ?) ) ; Table T1 has an index IX1 on (C1,C2,C3). If MULTI-ROW FETCH is specified, range-list access type is disabled. The optimal plan can be multi-index access using IX1 with MATCHCOLS=3. But Db2 does not build the multi-index plan because the estimated optimizer storage exceeds the maximum optimizer storage allowed. It results in an single index access with MATCHCOLS=1. Additional Keywords: SQLPERFORMANCE SQLACCESSPATH MIDX SQLOR
Problem conclusion
Code is updated to select a multi-index plan.
Temporary fix
Comments
APAR Information
APAR number
PH23827
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-03-30
Closed date
2020-04-20
Last modified date
2020-05-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI69114
Modules/Macros
DSNXOGCM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI69114
UP20/04/29 P F004
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2020