A fix is available
APAR status
Closed as program error.
Error description
For a query that contains a join predicate that is an expression with no supporting index on expression index, we can choose to build a sparse index on this inner table. But this is a cost decision even though a better solution may not exist. Therefore, we should favor building a sparse index on the inner table in most cases. Additional keywords and symptoms. SQLPERFORMANCE SQLSPIDX SPARSEINDEX
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users who run queries * * with join predicates referencing column * * expressions. * **************************************************************** * PROBLEM DESCRIPTION: * * Db2 may choose a nested loop join with * * with an R scan or an index scan that * * does not cover the join predicates * * when the query contains join * * predicates referencing column * * expressions. This can result * * in sub-optimal performance. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** Db2 may choose a nested loop join with a R scan or an index scan that doesn't cover join predicates, when the query contains join predicates referencing column expressions. It can cause sub-optimal performance when the sort merge join is not eligible and only sparse index access is eligible.
Problem conclusion
The performance problem described above is solved by PH22186 by detecting that neither the sort merge join nor the index plan is available to cover the join predicates referencing the column expression. When the above conditions are detected, the sparse index access will be favored. SQLWHERE SQLSPARSEINDEX SPARSEINDEX SQLPERFORMANCE
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PH22186
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-02-13
Closed date
2020-07-31
Last modified date
2020-09-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI70861
Modules/Macros
DSNXOCSC DSNXOPTH
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI70861
UP20/08/11 P F008 ¢
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"}],"Version":"12.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
02 September 2020