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

Obtain the fix for this APAR.

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