PM16611: ABEND04E RC00E20016 IN DSNSVSTK +0656 WHEN REPEATEDLY LOOPING OVER SAME SQL UPDATE STMT WITH BIND OPTION RELEASE(DEALLOCATE)

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E20016 IN DSNSVSTK +0656 WHEN REPEATEDLY LOOPING
  OVER SAME SQL UPDATE STMT WITH BIND OPTION RELEASE(DEALLOCATE)
  .
  DB2STGLK/K
  

Local fix

• REBIND THE APPLICATION PACKAGE THAT HAS THE FAILING UPDATE
  STATEMENT WITH BIND OPTION RELEASE(COMMIT)
  

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

• AM16611
  

Comments

APAR Information

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

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

  UK61648 UK61649

Modules/Macros

• DSNXECLC DSNXECLF DSNXECLN DSNXECW  DSNXECWA
  DSNXECWU
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK61648

     UP10/11/10 P F011

• R910 PSY UK61649

     UP10/11/10 P F011

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.