PI22477: ABEND04E RC00E72018 DSNXSZK OFFSET2EF6 WITH SELECT THAT INVOLVES IN-SUBQUERY PREDICATE WITH GROUP BY

Obtain the fix for this APAR.

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 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.