PM94081: INEFFICIENT ACCESS PATH IS CHOSEN WHEN THE QUERY CONTAINS LIKE OR BETWEEN PREDICATE DUE TO WRONG FILTER FACTOR ESTIMATION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When SYSCOLDIST contains all the frequency values of the column,
  that is the number of frequency values of the column is equal to
  the column cardinality, the filter factor estimation of LIKE or
  BETWEEN predicate is wrong, causes wrong access path to be
  used.
  
  For example,
  
   Column:   C1 VARGRAPHIC(56)    COLCARDF = 4
   Prdicate: C1 LIKE 'MBG%'
  
  Filter factor is estimated to be 1/4, while filter factor
  should be 0 based on the frequency statistics in SYSCOLDIST
  

Local fix

• opthint or query rewrite
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 and 11 for z/OS users of queries with *
  *                 LIKE predicates or BETWEEN predicates when   *
  *                 the statistics indicate that the number      *
  *                 of frequency values for the column is        *
  *                 equal to the column cardinality.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may select an inefficient access    *
  *                      path for a query with a LIKE predicate  *
  *                      or BETWEEN predicate if the column in   *
  *                      the predicate contains frequency        *
  *                      statistics and the number of frequency  *
  *                      values is equal to the column           *
  *                      cardinality.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may produce an inaccurate estimation of the filter factor
  for LIKE predicate or BETWEEN predicate if the following
  conditions are satisfied:
  
  1. There is a LIKE or BETWEEN predicate in the query.
  
  2. The column in the predicate has frequency statistics and the
  number of frequency values is equal to the column cardinality.
  
  For example:
  SELECT *
  FROM T1
  WHERE C1 LIKE 'ABC%';
  
  If the cardinality of C1 is 10 and there are 10 frequency
  values for C1, DB2 may produce an inaccurate estimation of the
  filter factor for the predicate C1 LIKE 'ABC%'.
  
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLLIKE SQLBETWEEN
  

Problem conclusion

• Code has been modified to produce an accurate filter factor for
  the cases described as 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:

  UK98288 UK98289

Modules/Macros

• DSNXODFB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK98288

     UP13/10/31 P F310

• RB10 PSY UK98289

     UP13/10/29 P F310

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.