IBM Support

PM65236: AB04E RC00E20016 IN DSNSVSTK +0656 AFTER REPEATEDLY EXECUTING SAME 'SEARCHED' SQL UPDATE WHERE STMT BOUND RELEASE(DEALLOCATE)

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • An application program repeatedly issued the same 'searched'
    SQL UPDATE / DELETE statement followed by COMMIT within
    a program loop for 1000s of iterations. Note that for 'searched'
    UPDATE / DELETE , the WHERE predicate does NOT specify a cursor
    name.  The following is a simplified example of the scenario
    described:
                                                                   .
      DO I = 1 to 5000;
                                                                   .
        EXEC SQL UPDATE TABLE1  SET COL1 = COL1 + 1
          WHERE  COL2 = 123 AND COL3 < 9999;
                                                                   .
        EXEC SQL COMMIT;
                                                                   .
      END;
                                                                   .
    The application program was bound with bind option
    RELEASE ( DEALLOCATE ). The application program was in a long-
    running thread that eventually hit the following or similar
    out-of-storage condition:
                                                                   .
     ABEND04E RC00E20016 in DSNSVSTK at offset 0656
                                                                   .
    Reviews of the storage pools in the DB2 dump show that the DB2
    thread's ADMF Agent Local Pools ( AGL VL and AGL 31 ) show
    an excessive amount of DB2 internal blocks with eyecatchers CU
    and SMSB .
                                                                   .
    This problem is specific to the 'serached' form of SQL UPDATE or
    DELETE , and does not occur for the 'positioned' or cursor-
    related form of SQL UPDATE / DELETE.
    The storage problem also does not occur when using bind option
    RELEASE(COMMIT).
    

Local fix

  • To circumvent this storage leak, REBIND the application as
    RELEASE(COMMIT) so that the storage for the CUB and SMSB are
    freed at COMMIT.
                                                                  .
    Additional search keywords: RELEASE DEALLOCATE SQLSTORAGE GROWTH
                                SQLLEAK SQLUPDATE SQLDELETE MERGE
                                OFFSET0656 OFFSET061A OFFSET05E6
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 users of bind option RELEASE(DEALLOCATE) *
    *                 with searched SQL UPDATE or DELETE statement *
    ****************************************************************
    * PROBLEM DESCRIPTION: An application program bound with bind  *
    *                      option RELEASE(DEALLOCATE) and          *
    *                      containing a program loop that issued   *
    *                      the same 'searched' SQL UPDATE / DELETE *
    *                      statement followed by COMMIT for 1000s  *
    *                      of iterations, eventually hit           *
    *                      the following or similar out-of-storage *
    *                      condition for a long running thread:    *
    *                                                            . *
    *                       AB04E RC00E20016 IN DSNSVSTK +0656     *
    *                                                            . *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An application program repeatedly issued the same 'searched'
    SQL UPDATE / DELETE statement followed by COMMIT within
    a program loop for 1000s of iterations. Note that for 'searched'
    UPDATE / DELETE , the WHERE predicate does NOT specify a cursor
    name.  The following is a simplified example of the scenario
    described:
                                                                   .
      DO I = 1 to 5000;
                                                                   .
        EXEC SQL UPDATE TABLE1  SET COL1 = COL1 + 1
          WHERE  COL2 = 123 AND COL3 < 9999;
                                                                   .
        EXEC SQL COMMIT;
                                                                   .
      END;
                                                                   .
    The application program was bound with bind option
    RELEASE ( DEALLOCATE ). The application program was in a long-
    running thread that eventually hit the following or similar
    out-of-storage condition:
                                                                   .
     ABEND04E RC00E20016 in DSNSVSTK at offset 0656
    After reviewing the storage pools in the dump, IBM saw that this
    DB2 thread's ADMF Agent Local Pools ( AGL VL and AGL 31 ) were
    unusually large.  Further examination of the storage segments of
    these two AGL pools  showed an excessive amount of DB2 control
    blocks with eyecatchers CU and SMSB .
                                                                   .
    IBM DB2 Development determined that the storage leaks for these
    two blocks were the result of DB2 not properly reusing the CU
    block (aka CUB) after a COMMIT for repeated execution of the
    same searched SQL UPDATE / DELETE when bind option
    RELEASE(DEALLOCATE) is in effect.  RELEASE(DEALLOCATE) behavior
    means that DB2 should not free some program and statement-level
    blocks at COMMIT, but instead keep them and reuse them for next
    execution of the same statement or program following the COMMIT.
    In this CUB and SMSB leak case, DB2 did not properly reuse
    these blocks after COMMIT for the 'searched' UPDATE and DELETE ,
    but instead allocated new CUB and SMSB blocks for these stmts
    following the COMMIT.  This then resulted in the excessive
    number of these blocks in the AGL 31 and AGL VL pools , followed
    by the 'DB2 out-of-storage' abend/s.
                                                                   .
    Other out-of-storage abends reported for the same pblm are --
      ABEND04E RC00E20003 DSNGEPLC . DSNSVBK +061A
      ABEND04E RC00E20003 DSNB1DRN . DSNSVBK +061A
      ABEND04E RC00E20003 DSNICUBD . DSNSVBK +061A
      ABEND04E RC00E20013 DSNSLD1  . DSNSGMN +05E6
                                                                   .
    This problem does NOT occur for the 'positioned' UPDATE/DELETE .
    For a 'positioned' UPDATE/DELETE , the WHERE predicate specifies
    a cursor name.
                                                                   .
    To circumvent this storage leak, REBIND the application as
    RELEASE(COMMIT) so that the storage for the DB2 thread's CUB and
    SMSB blocks are freed at the COMMIT.
                                                                   .
    

Problem conclusion

  • DB2 COMMIT and Thread-reuse/NEWUSER cleanup code for searched
    UPDATE/DELETE and MERGE statements was modified to honor
    the RELEASE(DEALLOCATE) behavior semantic for the DB2 internal
    CUB block that is used for the actual update/delete/merge
    operation.
                                                                   .
    Additional search keywords:  SQLSTORAGE SQLLEAK GROWTH
                                 SQLUPDATE SQLDELETE SQLMERGE
                                 OFFSET0656 OFFSET061A OFFSET05E6
                                 OFFSET
    

Temporary fix

  • AM65236
    

Comments

APAR Information

  • APAR number

    PM65236

  • 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-05-22

  • Closed date

    2012-06-20

  • Last modified date

    2012-08-08

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

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

    UK79701

Modules/Macros

  • DSNXECLF DSNXECLN DSNXECW  DSNXECWA DSNXECWU
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK79701

       UP12/07/08 P F207

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
08 August 2012