IBM Support

PH22197: ACCESS PATH DEGRADATION IN V12 DUE TO A RISK OF EXCEEDING A RID THRESHOLD

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In Db2 V12, there is a chance the access path will degrade
    during a SELECT using matching index access with list prefetch
    to access a table when there is a screening predicate with a
    host variable on the right side of an expression and the
    matching filter factor is poor.  The possible degradation is
    due to the RID List threshold being exceeded by prefetch at run
    time due to too many matching rows.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of Db2 12 for z/OS who have a                      *
    * query including two or more predicates and                   *
    * a host variable is the right hand side of a                  *
    * range predicate.                                             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Db2 may select an inefficient access                         *
    * path with list prefetch for a query                          *
    * which contains two or more predicates                        *
    * and a host variable is the right hand                        *
    * side of a range predicate, while the                         *
    * range predicate is a screening                               *
    * predicate for the selected index.                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    If a query contains two or more predicates and a host variable
    is the right hand side of a range predicate, Db2 may select an
    inefficient access path with list prefetch while the range
    predicate is a screening predicate for the selected index.
    Example:
    SELECT T.C4, T.C5 FROM T
    WHERE T.C1 = ?
    AND   T.C3 < ?
    ;
    Db2 may select index IX(C1,C2,C3) with list prefetch to access
    table T. For this index, the equal predicate is matching, and
    the range predicate is screening with a host variable at the
    right hand side. It may bring bad performance when the equal
    predicate has a bad filter factor.
    

Problem conclusion

  • Code is updated to select an efficient access path.
    If the filter factor of the matching predicate exceeds an
    internal RID limit, we will block the list prefetch plan.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PH22197

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-02-16

  • Closed date

    2020-03-11

  • Last modified date

    2020-03-19

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI68321

Modules/Macros

  • DSNXOCCX
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI68321

       UP20/03/19 I 1000

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.

[{"Business Unit":{"code":"BU048","label":"IBM Software"}, "Product":{"code":"SSEPEK","label":"DB2 for z/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":""},{"Business Unit":{"code":"BU048","label":"IBM Software"}, "Product":{"code":"SG19M","label":"APARs - z/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":""}]

Document Information

Modified date:
18 March 2020