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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM75509

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-22

  • Closed date

    2013-04-14

  • Last modified date

    2013-06-04

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM75509

Modified date:

2013-06-04

Translate my page

Machine Translation

Content navigation