PH22172: FIRST LEG IN A MULTI INDEX ACCESS PATH CONTAINS HIGH UNCERTAINTY PREDICATE RESULTING IN POOR PERFORMANCE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Multi index plans that contain a high uncertainty predicate,
  such as a range predicate using a host variable, can cause
  poor performance if the index is chosen as the leading index.
  
  
  Additional symptoms and keywords.
  SQLPERFORMANCE SQLMIDX
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users who run queries                    *
  * with multiple predicates so that multi-                      *
  * index access plan is eligible.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may choose multi-index plan as                           *
  * below.                                                       *
  *                                                              *
  * (1)the 1st index plan and the 2nd                            *
  * index plan only differ in one matching                       *
  *  predicate.                                                  *
  * (2) The different matching                                   *
  * predicate in the 1st index plan has                          *
  * big value in the column                                      *
  * DSN_PREDICAT_TABLE.UNCERTAINTY.                              *
  * The multi-index plan described above                         *
  * can cause sub-optimal performance.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 may choose multi-index scan as below.
  
  (1)the 1st index plan and the 2nd index plan only differ in one
   matching  predicate.
  (2) The different matching predicate in the 1st index plan has
  big value in the column DSN_PREDICAT_TABLE.UNCERTAINTY.
  The multi-index plan described above normally does not perform
  as well as single index plan because the extra matching
  predicate has high uncertainty.
  

Problem conclusion

• The performance problem described above is fixed by avoiding
  to use the index plan with high uncertainty in the
  non-overlapping matching predicate as the 1st index plan.
  The change can have access plan impact.
  SQLWHERE SQLACCESSPATH SQLPERFORMANCE
  

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:

  UI69769

Modules/Macros

• DSNXOGCM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI69769

     UP20/06/06 P F006

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.