PI34213: SPARSE INDEX ON THE INNER TABLE IS USED WHEN JOINING WITH RECURSIVE CTE IN V11

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sparse index is used when joining with recursive CTE in v11,
  that leads to bad performance.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users of recursive common    *
  *                 table expressions (CTE) with joins.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may select a nested loop join with  *
  *                      sparse index used on the inner table    *
  *                      for a join in a recursive common table  *
  *                      expression (CTE) when there are good    *
  *                      indexes on the inner table to support   *
  *                      the join predicates  This could lead    *
  *                      to poor query performance.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Sparse index may be chosen for a join in a recursive common
  table expression when there are good indexes on the inner table
  to support the join predicates.
  
  For example,
  
  WITH CTE1(C1, C2, C3) AS
    ( SELECT C1, C2, C3
      FROM T1 TA
      WHERE TA.C1 = 'A'
      UNION ALL
      SELECT C1, C2, C3
      FROM CTE1 TB
         , T2   TC
      WHERE TB.C1 = TC.C1
        AND TB.C2 = TC.C2
    )
  SELECT *
  FROM CTE1;
  
  There is an index IX1(C1, C2) on table T2. DB2 may select a
  nested loop join with sparse index on the inner table T2 for
  the join in the recursive common table expression CTE1 even
  when there is a good index IX1 on table T2.
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLCTE SQLNLJ SQLSPARSEINDEX
  SPARSEINDEX
  

Problem conclusion

• The code has been modified to disable sparse index for joins in
  a recursive common table expression when there are good indexes
  on the inner table to support the join predicates.
  

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:

  UI25958

Modules/Macros

•    DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI25958

     UP15/03/31 P F503

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.