PM84177: AN INEFFICIENT ACCESS PATH MAY BE SELECTED FOR A QUERY INVOLVING AN EMPTY TABLE OR A TABLE WITHOUT STATISTICS COLLECTED

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An inefficient access path may be selected for a query involving
    a table with CARDF = 0, a partitioned table with a partition
    whose CARDF = 0,  a volatile table without statistics
    collected or a table without statistics collected and subsystem
    parameter NPGTHRSH > 501.
    

Local fix

  • RUNSTATS
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 10 for z/OS users of dynamic queries     *
    *                 and static queries in a package bound with   *
    *                 REOPT(ALWAYS)                                *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 may select an inefficient access    *
    *                      path for a dynamic query or a static    *
    *                      query in a package bound with the       *
    *                      option REOPT(ALWAYS) if the query       *
    *                      references a table satisfying any of    *
    *                      the following conditions:               *
    *                      1. there are no statistics collected    *
    *                      on the table and the table is a         *
    *                      VOLATILE table;                         *
    *                      2. there are no statistics collected    *
    *                      on the table and the subsystem          *
    *                      parameter NPGTHRSH is -1 or is greater  *
    *                      than 501;                               *
    *                      3. the statistics in catalog shows      *
    *                      the table is empty;                     *
    *                      4. the statistics in catalog shows all  *
    *                      the qualified partitions are empty.     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    DB2 may select an inefficient access path for a dynamic query
    or a static query in a package bound with the option
    REOPT(ALWAYS) if the query references a table which satisfies
    any of the following conditions:
    
    1. there are no statistics collected on this table and the table
    is a VOLATILE table;
    
    2. there are no statistics collected on this table and the
    subsystem parameter NPGTHRSH is -1 or is greater than 501;
    
    3. The statistics in the catalog shows the table is empty;
    
    4. The statistics in the catalog shows all the qualified
    partitions of the table are empty.
    
    When any of the above conditions is satisfied, the DB2
    optimizer may use real time statistics (RTS) in access path
    cost estimation but RTS may not be used properly, which may
    lead to poor access path selection.
    
    Additional Keywords:
    SQLACCESSPATH SQLPERFORMANCE RTS
    

Problem conclusion

  • The code has been modified to utilize RTS properly for the
    queries described as above.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM84177

  • 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-03-05

  • Closed date

    2013-05-29

  • Last modified date

    2013-07-02

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

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

    UK94557

Modules/Macros

  • DSNXOPRS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK94557

       UP13/06/14 P F306

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

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM84177

Modified date:

2013-07-02

Translate my page

Machine Translation

Content navigation