PH21726: SLOW PERFORMANCE CAN OCCUR ON SOME QUERIES WHEN THE DESIRED INDEX IS NOT CHOSEN AND IT IS NOT MATCHING OR SCREENING

APAR status

• Closed as program error.

Error description

• Slow performance may occur on a query when the desired index
  is not chosen because the desired index is not matching or
  screening, is not index-only, and is not providing either
  GROUP BY grouping or ORDER BY order.
  ZSA45
  

Local fix

• Update hidden zparm.  (contact IBM support)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users who have                           *
  * manually edited SDSNMACS(DSN6SPRM)                           *
  * and have tables with multiple indexes                        *
  * defined on them.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Slow performance may occur for a                             *
  * query involving a table with                                 *
  * multiple indexes defined. The                                *
  * poor performing access path does                             *
  * not select the index that provides                           *
  * good performance.                                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Slow performance may occur for a query involving one or
  more tables with multiple indexes.  When a table has
  multiple indexes defined, Db2 may fail to select the
  best performing index, resulting in poor performance.
  This is more likely to occur when the indexes have one
  or more columns in common.  For example:
  Index 1: (C1, C2, C3)
  Index 2: (C4, C1, C2, C3)
  
  Additional Keywords:
  IDAAV7R1/K IDAAV7R5/K IDAAV5R1/K
  

Problem conclusion

• Db2 code is improved to better handle the case of multiple
  indexes on the same table.
  Users of IBM Db2 Analytics Accelerator are strongly encouraged
  to apply this fix, in conjunction with PH28050, to reduce
  possible spikes in offload activity.
  

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:

  UI71209

Modules/Macros

• DSNXOMPS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI71209

     UP20/09/01 P F008