PH13674: EXCESSIVE GETPAGE ON INDEX FOR A MATCHING INDEX SCAN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A matching index access scans more index pages than expected.
  
  SELECT *
  FROM T1
  WHERE C1 IN ('A','B','C','D')
    AND C2=?
    AND C3>?
  GROUP BY C3;
  
  Index IX1 on T1 is on (C1,C2,C3). Index IX1 is used with IN list
  access(ACCESSTYPE=N) and 3 matching columns(MC=3), sort for
  GROUP BY C3 is avoided by this index access.
  
  When DB2 does MC=3 index access for C1='C' AND C2=? AND C3>?, it
  losts the matching columns of C2 and C3, it only uses MC=1
  (C1='C'), thus all leaf pages under C1='C' are scanned.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Db2 11 for z/OS and Db2 12 for z/OS users    *
  *                 using indexes                                *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQL query using index access            *
  *                      with matching index scan could          *
  *                      scan more index pages than              *
  *                      expected if the SQL query uses          *
  *                      SELECT DISTINCT or GROUP BY clause.     *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  SQL query using index access with matching index scan
  could scan more index leaf pages than expected when
  reverse index scan is used and the SQL query uses
  SELECT DISTINCT or GROUP BY clause.
  During SQL query execution, index access returns distinct
  values in order to avoid sort for the SELECT DISTINCT or
  GROUP BY clause. When index scan searches for the next
  distinct value, Db2 reads index leaf pages in sequential
  order instead of traversing to the next distinct value,
  which causes excessive index get page request.
  

Problem conclusion

• Db2 code has been updated to traverse to the next distinct value
  instead of scanning the index leaf pages sequentially during
  reverse index scan when the SQL query uses SELECT DISTINCT
  or GROUP BY clause with index access. This will avoid excessive
  index get page requests when the SQL uses reverse index scan to
  return distinct values for SELECT DISTINCT or GROUP BY.
  

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:

  UI65165 UI65166

Modules/Macros

•    DSNKFPRV
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI65166

     UP19/09/21 P F909

• RC10 PSY UI65165

     UP19/09/21 P F909

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.