A fix is available
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 number
PM16611
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2010-06-15
Closed date
2010-10-25
Last modified date
2011-06-01
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
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":"9.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":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 June 2011