PI99267: AVOID A POOR FILTER FACTOR WHEN A DATE OR TIME RELATED SPECIAL REGISTER FALLS OUTSIDE THE RANGE STORED IN THE CATALOG.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Beginning in v12, Db2 is able to resolve DATE/TIME/TIMESTAMP
  special registers to their actual values. Normally this would
  result in a better filter factor and ultimately a better
  access path. However for range predicates that fall outside the
  values contained in HIGH2KEY/LOW2KEY, a poor filter could be
  calculated. This is because DATE/TIME/TIMSTAMP values stored in
  the catalog age very rapidly and are virtually impossible to
  keep current. This APAR addresses this issue and essentially
  eases the requirement for stats to be perfect on
  date/time/timestamp columns.
  
  
  Additional keywords and symptoms:
  SQLPERFORMANCE SQLTIMESTAMP SPECIALREGISTER RANGEPREDICATE
  SQLDATE SQLTIME
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who use                         *
  * predicate COL op CURRENT DATE or                             *
  * COL op CURRENT TIMESTAMP.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When a query uses predicate such as                          *
  * COL op CURRENT DATE or                                       *
  * COL op CURRENT TIMESTAMP,                                    *
  * the query may not get the optimal                            *
  * access path because the filter factor                        *
  * of the described predicate could be                          *
  * too small.                                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  For queries described above, Db2 may choose a non-optimal
  access path because it may estimate the predicate filter
  factor lower than it should be. If the predicate can find
  an eligible index to run index probing, the low filter factor
  value will be corrected. Otherwise, it may cause a non-optimal
  access path.
  

Problem conclusion

• The low filter factor is caused by using the special register
  value. In this fix it is solved by saving and restoring the
  filter factor calculated without using the special register
  value.
  
  Additional keywords:
  FILTERFACTOR SQLACCESSPATH
  

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:

  UI57161

Modules/Macros

• DSNXODFE DSNXODFB DSNXODFR DSNXOPRP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI57161

     UP18/07/25 P F807

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.