PM89500: AN EXCESSIVE STORAGE ALLOCATION MAY OCCUR WHEN EXECUTING PACKAGES UNDER A STORED PROCEDURE BOUND WITH KEEPDYNAMIC (YES)

APAR status

• Closed as program error.

Error description

• AN EXCESSIVE STORAGE ALLOCATION MAY OCCUR WHEN EXECUTING
  PACKAGES UNDER A STORED PROCEDURE BOUND WITH KEEPDYNAMIC (YES)
  AND STATEMENT CACHE IS ENABLED
  The storage map shows multiple 'CACHED SQL STMTS' storage
  pools and within each pool there are SPRC blocks.
  The owner of the SPRC blocks can be gone already.
  7F6687A0  CACHED SQL STMTS             VAR          31772K
  7F6687A0  CACHED SQL STMTS             VAR          31772K
  7F668BB0  CACHED SQL STMTS             VAR          31260K
  7F6686D0  CACHED SQL STMTS             VAR          31260K
  7F668AE0  CACHED SQL STMTS             VAR          31772K
  7F668600  CACHED SQL STMTS             VAR          31772K
  

Local fix

• CHANGE THE BIND OPTION OF THE IMPACTED APPLICATION WITH
  KEEPDYNAMIC (NO)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 Users who bind stored procedures     *
  *                 with KEEPDYNAMIC(YES) bind option            *
  *                 and execute cached dynamic statements in     *
  *                 the stored procedure or UDF.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: When applications execute cached        *
  *                      dynamic statements in stored procedures *
  *                      bound with the KEEYDYNAMIC(YES)         *
  *                      bind option, they could potentially     *
  *                      suffer a DB2 storage shortage           *
  *                      condition.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a stored procedure contains a dynamic statement and is
  bound with the KEEPDYNAMIC(YES) bind option, a COMMIT in the
  stored procedure only does a partial cleanup and the dynamic
  statement COPY COUNT is not decremented.
  
  After exit from the stored procedure, a COMMIT should have
  performed a complete cleanup including the decrementing of the
  dynamic statement COPY COUNT.
  
  A non-zero COPY COUNT can lead to the statement storage not
  being freed even after the dynamic statement was invalidated
  and no thread is accessing the statement. This can cause
  storage shortage.
  

Problem conclusion

• The error is corrected so the COPY COUNT is decremented after
  exit from the stored procedure.
  
  Additional Keywords:
  SQLKEEPDYNAMIC SQLSTOREDPROCEDURE SQLSP
  SQLSTORAGE DB2STGLK/K SQLDYNSTMTCACHE
  

Temporary fix

Comments

APAR Information

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

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

  UK95579

Modules/Macros

• DSNXECW  DSNXECWA DSNXECWU
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY

     UP