PI93452: PERFORMANCE IMPACT FOR QUERY WITH A NOT EXISTS PREDICATE WITH CORRELATED SUBQUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Performance impact for query with a NOT EXISTS predicate with
  correlated subquery, and there are two or more tables referenced
  in that subquery.
  Sample query:
  SELECT   DISTINCT B.COL1
           ,B.COL2
           ,B.COL3
           ,'R'
   FROM     TABLE2 B, TABLE3 C, TABLE4 D, TABLE6 F
   WHERE    C.COL1 = :H
     AND    C.COL2 = :H
     AND    B.COL1 = C.COL1
     AND    B.COL2 = C.COL2
     AND    D.COL4 = :H
     AND    F.COL5 IN('F', 'S')
     AND    B.COL6 >.70
     AND    C.COL3 = D.COL3
     AND    C.COL3 = F.COL3
     AND    C.COL3 = B.COL3
     AND    B.COL7 =
            (SELECT  MIN(E.COL7)
            FROM     TABLE5 E
            WHERE    E.COL1 = B.COL1
              AND    E.COL2 = B.COL2
              AND    E.COL3 = B.COL3
              AND    E.COL8 > :H
            )
     AND    NOT EXISTS
            (SELECT  *
            FROM     TABLE1 A, TABLE7 G
            WHERE    G.COL1 = C.COL1
              AND    G.COL2 = C.COL2
              AND    A.CNY_CD = G.CNY_CD
              AND    A.SLC_NR = G.SLC_NR
              AND    C.COL3 = A.COL3
            )
  Besides, this APAR also corrects a problem that Db2 populates
  incorrect TOTAL_COST to DSN_STATEMNT_TABLE, when a query refers
  to more than one table with FETCH FIRST n ROWS ONLY or OPTIMIZE
  FOR n ROWS.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who have a                      *
  * query with EXISTS/NOT EXISTS correlated                      *
  * subquery, or FETCH FIRST n ROWS ONLY or                      *
  * OPTIMIZE FOR n ROWS.                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may select an inefficient access                         *
  * path when a query with an EXISTS or                          *
  * NOT EXISTS correlated subquery. Also,                        *
  * Db2 populates an incorrect TOTAL_COST                        *
  * to DSN_STATEMNT_TABLE, if the query                          *
  * refers to more than one table with                           *
  * FETCH FIRST n ROWS ONLY or                                   *
  * OPTIMIZE FOR n ROWS.                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  If a query has an EXISTS/NOT EXISTS correlated subquery, Db2
  may get an incorrect subquery cost, which may cause Db2 to
  select an inefficient access path when accessing the outer
  table. Also, Db2 populates incorrect TOTAL_COST to
  DSN_STATEMNT_TABLE, when the query refers to more than one
  table with FETCH FIRST n ROWS ONLY or OPTIMIZE FOR N ROWS,
  due to getting incorrect total cost in this situation.
  
  Example:
  SELECT A.C1,B.C1
  FROM A,B
  WHERE A.C2=B.C2
  AND A.C3=?
  AND NOT EXISTS
  (SELECT * FROM C
   WHERE A.C4=C.C1
  );
  
  Db2 get incorrect correlated subquery cost, which result in Db2
  select inefficient join order when accessing table A,B.
  
  Additional keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLEXISTS SQLSUBQUERY SQLCORRSUBQ
  SQLFFNR SQLOFNR
  

Problem conclusion

• Code was updated to solve the problems described above.
  

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:

  UI55570

Modules/Macros

• DSNXOTS  DSNXOPTH DSNXOTS1 DSNXOTS2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI55570

     UP18/05/15 P F805

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.