PI37614: MULTI-INDEX INTERSECTION ACCESS PATH SELECTED WITH REDUNDANT INDEX ACCESS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 may incorrectly include an index to a multi-index
  intersection plan that provides the same matching coverage as
  another index and a subset of the screening coverage.  In this
  case, the index provides no additional value and should not be
  included in the multi-index intersection plan.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 for z/OS and DB2 11 for z/OS users.   *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may choose to include an index in   *
  *                      a multi-index intersection plan that    *
  *                      provides no value.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may select a multi-index intersection access path that
  includes an index that doesn't provide any additional filtering
  benefit.  For example...
  
  SELECT ...
  FROM T1
  WHERE C1 LIKE ?
    AND C2 = ?
    AND C3 = ?
  
  With indexes IX1(C1,C2) and IX2(C1,C2,C3), there is no value
  in including both indexes in the multi-index intersection plan
  as IX2 alone will provide all of the filtering from IX1.
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLMULTIINDEX
  

Problem conclusion

• DB2 code has been changed to avoid adding a redundant index
  to a multi-index intersection plan.
  

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:

  UI27771 UI27772

Modules/Macros

• DSNXOGCM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI27771

     UP15/06/04 P F506

• RB10 PSY UI27772

     UP15/06/04 P F506

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.