PH34178: SUB-OPTIMAL ACCESS PATH CAN BE CHOSEN FOR THE QUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The EQUAL correlated subquery predicate filtering can be
  overestimated when one or more of the correlation column(s) is
  the primary key.
  On V12,Db2 chooses join predicate pushdown and it can lead to
  sub-optimal performance.
  
  KEYWORDS: SQLACCESSPATH  	SQLCORRSUBQ  	SQLEQUAL PERFM
  SQLPERFORMANCE  	SQLPREDPUSHDOWN
  

Local fix

• BYPASS/CIRCUMVENTION:
  N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who have                        *
  * queries with the predicates that                             *
  * reference subquery.                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may overestimate the filter                              *
  * factor of the predicates referencing                         *
  * subquery when the following                                  *
  * conditions are met.                                          *
  * (1) One of the columns from the outer                        *
  * query block that is referenced in                            *
  * the subquery block is unique, or                             *
  * (2) The column from the SELECT list                          *
  * of the subquery block is unique, and                         *
  * (3) Both sides of the predicate are                          *
  * from identical table.                                        *
  * It may cause sub-optimal performance                         *
  * because the estimate error of the                            *
  * filter factor.                                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 may overestimate the filter factor of the predicates
  referencing subquery when the following conditions are met.
  (1) One of the columns from the outer query block that
  is referenced in the subquery block is unique, or
  (2) The column from the SELECT list of the subquery block
  is unique, and
  (3) Both sides of the predicate are from identical table.
  The overestimate of the filter factor is due to lack of
  cardinality stats.
  

Problem conclusion

• PH34178 fixes the filter factor estimate problem by using
  the table cardinality to help the ffilter factor
  calculation.
  SQLWHERE SQLSUBQUERY SQLACCESSPATH SQLPERFORMANCE
  

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:

  UI75206

Modules/Macros

• DSNXOINQ
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI75206

     UP21/05/12 P F105

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.