PM75509: V10 NFM ON UTS ONLY: SQL UPDATE MAY MOVE ROW TO NEW PAGE EVEN WHEN SPACE STILL EXISTS ON ORIGINAL PAGE,LEADING TO SPACE GROWTH

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This problem only exists in V10 NFM on universal table space
  (UTS) with row-level locking.
  .
  SQL UPDATE may move the row being updated to a new page even
  through there was enough space on the original page. This leads
  to unnecessary space growth.
  .
  If DSNIREPR redrive is passed control, then xOldLen needs to
  have correct OLEN value.
  .
                                                     (d159043)
  Sample recreation:
  ------------------
  1. Create table space w/32K pages.
  2. Create a table therein with max row size on the order of
     20,000 bytes. Commit.
  3. Insert a 17,000 byte record. Commit.
  4. Update the record (new length is 17,003).
  .
  Pointer record + overflow record created when the row should
  have stayed on the same page.
  .
  The problem equally applies to cases when there are multiple
  rows on a page, e.g. 100 bytes are still left at the end of the
  page and a row in the middle of the page is extended by 3 bytes.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS New Function Mode (NFM)  *
  *                 users of Universal Table Space (UTS)         *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected space growth due to          *
  *                      unnecessary overflowing or double-      *
  *                      overflowing of records during SQL       *
  *                      UPDATE against UTS in DB2 10 NFM        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For an SQL UPDATE, if the new record length is larger than the
  old record length, DB2 will check the current page if there is
  enough room to hold the new record. If there is not, DB2 needs
  to move the record to a new page called overflow page. Prior to
  this fix, DB2 might use an incorrect old length of record to
  check the room and might move the record to an overflow page
  even though there is enough room on the base page to contain the
  new record.
  
  Two symptoms can be observed:
  (1) Unnecessary overflowing of record in base page
  (2) Unnecessary double-overflowing
  
  Sample recreation:
  ------------------
  1. Create a table space with 32K pages.
  2. Create a table therein with a maximum record size on the
     order of 20,000 bytes. Commit.
  3. Insert a 17,000-byte record. Commit.
  4. Update the record (new length is 17,003).
  .
  A pointer record plus an overflow record are created even though
  the record should have stayed on the same page.
  
  The problem equally applies to cases when there are multiple
  records on a page, e.g. 100 bytes are still left at the end of
  the page and a record in the middle of the page is extended by
  3 bytes.
  

Problem conclusion

• DB2 code has been modified to make sure that the correct old
  length of a record is used when checking for free space.
  
  Additional keywords: SQLUPDATE
  

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:

  UK93439

Modules/Macros

• DSNIREPR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK93439

     UP13/05/02 P F305

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.