A fix is available
APAR status
Closed as program error.
Error description
DUMP TITLE=DB2A,ABND=04E-00E72018,U=SYSADM ,M=N ,C=111.RDS -S QL ,M=DSNTFRCV,LOC=DSNXGRDS.DSNXSZK +2EF6 DB2 failed to build a sort plan for IN-subquery predicate processing when the subquery block has GROUP BY with a set function and the set function result is used in the IN-subquery predicate evaluation.
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: DB2 11 for z/OS users who have queries * * with IN-subquery predicates and a GROUP BY * * clause in the IN-subquery. * **************************************************************** * PROBLEM DESCRIPTION: ABEND04E RC00E72018 in DSNXSZK * * offset 2EF6 may occur when * * running a SELECT query with all * * of the following conditions: * * * * (1) It contains an IN-SUBQUERY * * predicate * * (2) In the subquery block, it * * contains a GROUP BY clause and one * * MAX / MIN set function * * (3) DSN_DETCOST_TABLE. * * IXSCAN_SKIP_DUPS in the subquery * * has value YES. * **************************************************************** * RECOMMENDATION: * **************************************************************** ABEND04E RC00E72018 in DSNXSZK offset 2EF6 may occur when running a SELECT query with an IN-SUBQUERY predicate, GROUP BY clause, and one MAX / MIN set function. The abend occurs because for the subquery processing DB2 chooses an access plan that is out of the expected order. For example, SELECT C1, C2, C3, C4 FROM T1 WHERE C1 = 'I' AND C2 = 'M' AND (C3) IN (SELECT MAX(C3) FROM T2 GROUP BY C2 ) ORDER BY 1, 3; There exists an index on table T2 as INX1_T2 on (T2.C2, T2.C3). For the query above, if the index INX1_T2 is used in the subquery block and DSN_DETCOST_TABLE.IXSCAN_SKIP_DUPS is YES, the abend may occur because the subquery output is not in the expected order.
Problem conclusion
The APAR fixes the problem by choosing an access plan that is in the expected order during subquery processing. As a result, the access plan may change for queries with predicates referencing a subquery and may cause a performance impact. KEYWORDS: SQLWHERE SQLIN SQLGROUPBY SQLACCESSPATH SQLINSUBQ ABEND04E OFFSET2EF6 SQLMAX SQLMIN
Temporary fix
Comments
APAR Information
APAR number
PI22477
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2014-07-22
Closed date
2014-11-12
Last modified date
2015-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI22986
Modules/Macros
DSNXOPRP
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI22986
UP14/12/02 P F412
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:
02 January 2015