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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM94081

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-30

  • Closed date

    2013-10-14

  • Last modified date

    2013-11-04

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM94081

Modified date:

2013-11-04

Translate my page

Machine Translation

Content navigation