PH36737: RIDPOOL FAILURES FOR QUERY WITH MULTI-INDEX ANDING AND NO QUALI FYING RIDS FOR SECOND INDEX

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The query was using 2 index ANDing and the second index returned
  zero qualified RIDs after the index scan.  Old RID blocks that
  were no longer needed were not properly released and ended up
  consuming all of the RID pool storage.
  
  In this case, the multi-index access of 2 index ANDing is used
  to access PLAN_TABLE.
  INDEX1 is on column (PROGNAME, COLLID, BIND_TIME), and the index
  scan matches all 3 columns.
  INDEX2 is on column (QUERYNO and 5 other columns) and
  the index scan matches the first column but does not
  return any rows. This triggers the problem of not releasing
  the old RID blocks.
  
  A possible bypass is to use an optimization hint
  to use a single index scan, INDEX1 which matches 3 columns,
  in this case.
  Additional keywords:
  MIDX RC00C9003E
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of correlated                      *
  * subqueries with multiple index access.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * RID pool failures can occur due to                           *
  * short-on-storage or poor/degraded                            *
  * query performance when running a query                       *
  * with a correlated subquery using                             *
  * multiple index access of index                               *
  * intersection,                                                *
  * (ACCESSTYPE='M', 'MX' and 'MI' only).                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When running a query with a correlated subquery using multiple
  index access of index intersection (ACCESSTYPE='M', 'MX'
  and 'MI' only), Db2 might not properly release intermediate
  RID lists generated during the processing and end up holding
  onto a large amount of RID pool storage. This can result in a
  RID pool failure or poor/degraded query performance for
  this query, and affect other concurrent queries using RID list
  processing.
  Here is an example query to help understand the problem.
    SELECT C3, C4 FROM T1
    WHERE NOT EXISTS
      (SELECT 1 FROM T2
       WHERE T2.C1 = T1.C1
       AND T2.C2 = T1.C2
       AND T2.C3 = xxx);
  In this example, the query contains a correlated subquery
  of NOT EXISTS. Assume there is an index IDX1 on T2.C1 and
  IDX2 on T2.C2, and that multiple index access of index
  intersection between IDX1 and IDX2 is used when accessing
  table T2, the above mentioned problem might happen.
  

Problem conclusion

• Db2 code has been modified to free the appropriate RID pool
  storage properly (and timely) which will prevent the above
  mentioned problem.
  Additional Keywords: SQLRID SQLMIDX MIDX SQLCORRSUBQ
  

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:

  UI76363

Modules/Macros

• DSNXRRID
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI76363

     UP21/07/27 P F107

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.