PM77236: UNEXPECTED TABLESPACE GROWTH DURING UPDATE'S WHEN ROW-LEVEL-LOCKING & SMALL MAXROWS VALUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Tablespaces may not be using available space on a page for
  UPDATE's, causing unexpected tablespace growth.
  .
  This growth problem may occur when all of the following occur:
  .
   1) Row-Level-Locking is in affect.
   2) Multiple units-of-work are updating the same page at the
      same time.  Some change must be affected holes (shrinking
      or deleting a row).
   3) The page already has MAXROWS number of rows on it.
  .
  When space is being looked for on a page, but another update
  is already in progress on the same page that affect holes, by
  either changing a row to be smaller or deleting a row, then
  the holes on that page cannot be used at this point.
  The contiguous freespace at the end of the page, if any,
  might be able to be used.
  .
  The current code, at the point described above, checks the
  number of rows, to see if it is already at the MAXROWS value,
  and if it is, then other pages are then checked for space.
  This logic is correct, if the operation is an INSERT, but not
  correct for an UPDATE, as it would not be adding a new row, and
  the existing space on the page could have been used.
  

Local fix

• Increase the MAXROWS value.
  Change to Page-Level-Locking.
  Avoid updates to same pages, if possible.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 and 10 for z/OS users of tablespaces   *
  *                 with MAXROWS attributes under RRL - row      *
  *                 level locking.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected Growth of tablespaces        *
  *                      under massive concurrent delete and     *
  *                      update operations.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Under the following circumstances, the tablespace can grow:
   (1) The tablespace is defined without hash organization.
   (2) Row-Level-Locking is in affect
   (3) Multiple units-of-work are updating the same page
       at the same time and the total RIDs already reached the
       MAXROWS.
   (4) The page has reached the MAXROWS limit, but the page
       has contiguous free space to hold updated row size.
  Today, DB2 code verifies the MAXROWS limit first and eliminates
  the need to use contiguous free space within the page during
  the UPDATE operation which creates a bigger size of row.
  As a result, the updated row was processed as an overflow
  record which is then inserted to other data pages and caused
  more data pages to be created.
  

Problem conclusion

• DB2 code is modified to ignore the MAXROWS checking for UPDATE
  operation which allows the UPDATE to use the contiguous free
  space if it is adequate.
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK90725 UK90726

Modules/Macros

• DSNIREPR DSNISGRT DSNISMRT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90725

     UP13/01/19 P F301

• R910 PSY UK90726

     UP13/01/19 P F301

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.