PI95063: REMOVE HISTOGRAMS FROM BEING USED FOR JOIN PREDICATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR enhances optimizer costing here to better reflect the
  fact that the execution may or may not actually utilize the
  highly skewed value (eg. when histograms are collected)
  
  The collection of HISTOGRAM statistics on join predicates may
  result in the Optimizer mis-estimating the number of rows
  processed across a join or correlated subquery which could
  result in an inefficient index choice or tablespace scan being
  chosen on the inner table, or an inefficient join sequence. This
  is more likely if there exists unevenly distributed data on the
  join column(s) or different ranges of data between the tables
  which may also be the result of RUNSTATS with HISTOGRAM
  statistics being collected on join columns of each table at
  different times.
  
  Keywords:
  SQLJOIN
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 11 and Db2 12 for z/OS with                 *
  * a correlated subquery.                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may select an inefficient access                         *
  * path for a correlated subquery.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 may select an inefficient access path for a table in a
  correlated subquery, when data on a join column is skewed.
  
  Example:
  SELECT A.C1, A.C2 FROM T A
  WHERE A.C1 < ?
  AND NOT EXISTS (SELECT 1 FROM T B
  WHERE B.C2 = A.C2
  AND B.C3 = ?);
  
  If there is an index IX1(C2) on table T, and data on C2 is
  skewed, Db2 may select an inefficient R-SCAN for table B
  instead of index IX1. The reason is that Db2 uses the
  histogram statistics of C2 to calculate the filter factor
  of the join predicate, and overestimates it.
  
  Additional Keywords:
  SQLACCESSPATH SQLPERFORMANCE SQLJOIN SQLSUBQUERY
  

Problem conclusion

• Code was updated to select an efficient access path.
  

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:

  UI56569 UI56580

Modules/Macros

• DSNXODFE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI56569

     UP18/06/29 P F806

• RB10 PSY UI56580

     UP18/06/30 P F806

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.