PM80173: POOR PEFORMANCE FOR QUERIES WITH RECURSIVE CTE WHICH CONTAINS MATERIALIZED TABLE EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 may select an inefficient access path for a query with
  a recursive CTE which contains materialized view or materialized
  table expression.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 for z/OS users of recursive common    *
  *                 table expressions (CTE) with joins.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may select a nested loop join and   *
  *                      a table space scan without sparse       *
  *                      index used on the inner table for a     *
  *                      join in a recursive common table        *
  *                      expression (CTE), which could lead to   *
  *                      poor query performance.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Sparse index is disabled for a join in a recursive common
  table expression.  It is likely DB2 selects a nested loop join
  and a table space scan on the inner table for a join in a
  recursive common table expression if there is no good index on
  the inner table to support the join predicates, or the inner
  table is a materialized table expression or a materialized view.
  
  For example,
  
  CREATE VIEW V1 AS
  SELECT C1 VC1, C2 VC2, MAX(C3) VC3
  FROM T1
  GROUP BY C1, C2;
  
  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
         , V1 TC
      WHERE TB.C2 = TC.VC2
    )
  SELECT *
  FROM CTE1;
  
  V1 is a view which needs to be materialized in the query above.
  DB2 will select a nested loop join and a table space scan on
  the inner table V1 for the join in the recursive common table
  expression CTE1.
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLCTE SQLNLJ SQLVIEW
  

Problem conclusion

• Code has been modified to enable sparse index for joins in a
  recursive common table expression.
  

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:

  UK92081

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK92081

     UP13/03/15 P F303

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.