PH49602: SPARSE INDEX MIGHT BE CHOSEN AND CAUSE PERFORMANCE REGRESSION BECAUSE OF COST ESTIMATED TOO HIGH WITH NO PENALTY.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sparse index might be chosen and cause performance regression
  because of cost estimated too high with no penalty.
  Code fix will add cost penalty for sparse index access if the
  sparse index size is bigger than the sparse index pool size and
  the outer composite cardinality is 5 times bigger than the inner
  table/workfile size after applying all the local predicate(s).
  
  ADDITIONAL SYMPTOMS:
  SQLPERFORMANCE SQLACCESSPATH SQLLEFT SQLLEFTJOIN
  

Local fix

• BYPASS/CIRCUMVENTION:
  You may try to write hint to choose access path that avoids use
  of sparse index.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 and Db2 13 for z/OS                      *
  * using big-size sparse indexes.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may choose                                               *
  * PLAN_TABLE.PRIMARY_ACCESSTYPE 'T' on                         *
  * a table/view/table expression when                           *
  * the following conditions are met.                            *
  * (1) The sparse index size is bigger                          *
  * than the maximum storage allocated                           *
  * for the sparse index, and                                    *
  * (2) The outer size is much larger                            *
  * than the inner table size after all                          *
  * local predicates are applied.                                *
  * (3) The size of sparse index key                             *
  * portion is less than half of the                             *
  * maximum storage allocated for the                            *
  * sparse index.                                                *
  * It can cause sub-optimal performance.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 may choose PLAN_TABLE.PRIMARY_ACCESSTYPE 'T' on a
  table/view/table expression when the following conditions
  are met.
  (1) The sparse index size is bigger than the maximum storage
  allocated for the sparse index, and
  (2) The outer size is much larger than the inner table
  size after all local predicates are applied.
  (3) The size of sparse index key portion is less than half of
  the maximum storage allocated for the sparse index.
  When the conditions above are met, a workfile will be used
  to build the sparse index. It can cause sub-optimal
  performance to access big sparse index stored in the workfile.
  

Problem conclusion

• PH49602 will disable using the sparse index on the
  table/view/table expression when the conditions above are
  detected.
  SQLSPARSEINDEX SPARSEINDEX 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:

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI83591

     UP22/12/14 P F212

• RD10 PSY UI83592

     UP22/12/14 P F212

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.