A fix is available
APAR status
Closed as program error.
Error description
In a SQL Query statement, there is local predicate in the parent queryblock which covers the column of correlated join column. And Db2 overestimates cost to access the subquery. . Sample Query: SELECT * FROM Table1 A WHERE keycol_1 = 'literal' AND Decimal_col IN (44,13) AND keycol_3 = (SELECT MAX(keycol_3) FROM Table1 B WHERE keycol_1 = 'literal' AND A.Decimal_col = B.Decimal_col) ; When a query contains a correlated subquery, and there is only one table in parent query block, the total cost of subquery is overestimated even though correlated join column is covered by local predicate in parent query block. In this scenario, subquery should get cost reduction. . Additionally, there are unusual high values for PROCMS and PROCSU in DSN_STATEMNT_TABLE, while the query itself runs in seconds. --------------------------------------------------------------- | STMT_TYPE | COST_CATEGORY | PROCMS | PROCSU | --------------------------------------------------------------- | SELECT | A | 15324080 | 567558656 | --------------------------------------------------------------- . In this particular case, overestimation of costs leads to interception of Query by Resource Limit Facility ( RLF ). Keywords: PERFM SQLCORRSUBQ
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: Db2 11 and Db2 12 for z/OS users who have a * * query that includes a correlated subquery, * * and all correlated join columns are covered * * by local EQUAL/IN predicates in the parent * * query block. * **************************************************************** * PROBLEM DESCRIPTION: Db2 may overestimate the cost to access * * a correlated subquery when all the * * correlated join columns are covered * * by local EQUAL or IN predicates. * **************************************************************** * RECOMMENDATION: Apply corrective PTF when available. * **************************************************************** When a query includes a correlated subquery, and all the correlated join columns are covered by local EQUAL/IN predicates in the parent query block, Db2 may overestimate the cost to access the subquery. Example: SELECT * FROM T1 WHERE T1.C1 = ? AND T1.C2 IN (?, ?, ?) AND T1.C3 = (SELECT MAX(T2.C3) FROM T2 WHERE T2.C1 = T1.C1 AND T2.C2 = T1.C2) ; T1.C1 and T1.C2 are correlated join columns, and there are local EQUAL/IN predicates that cover these columns in the parent query block. Db2 may overestimate the cost to access the correlated subquery. Additional keywords: SQLPERFORMANCE SQLACCESSPATH SQLSUBQUERY SQLCORRSUBQ
Problem conclusion
Db2 code has been updated to get a reasonable cost reduction to access a correlated subquery in the situation described above.
Temporary fix
Comments
APAR Information
APAR number
PH09814
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-03-19
Closed date
2019-05-29
Last modified date
2019-07-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI63288 UI63290
Modules/Macros
DSNXOCSH DSNXOCSM DSNXOCSN DSNXOPRP DSNXOTS
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 July 2019