PM07842: REPEATED INVOCATION OF RELEASE(DEALLOCATE) NESTED STORED PROCEDURE SCENARIO RESULTED IN EDM POOL FULL CONDITION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• User repeatedly invoked a nested stored procedure scenario where
  the calling stored proc was bound RELEASE ( DEALLOCATE ) and
  used static ALLOCATE CURSOR stmt to access result set from inner
  stored procedure.  Eventually EDM POOL FULL condition occurred.
  The RELEASE(DEALLOCATE) usage, as expected and designed,
  prevented a COMMIT outside of the stored procedures from freeing
  the EDM POOL storage.  However, DB2 Dev examined the
  EDM POOL FULL dump and saw that most of the EDM POOL space for
  the stored procedure package bound REL(DEALLOC) was occupied by
  exceedingly high number of the identical static ALLOCATE CURSOR
  stmt. DB2 should not have loaded so many copies of that
  ALLOCATE CURSOR stmt for this single package in EDM POOL.
  
  That DB2 error combined with usage of REL(DEALLOC) bind option
  (which prevented DB2 from releasing the EDM POOL space at
  COMMIT) caused the reported EDM POOL FULL condition.
  

Local fix

• REBIND the stored procedure package and specify bind option
  RELEASE(COMMIT), and if not already present, add a COMMIT
  outside of the stor procedure in the non-stored procedure
  calling application. That will allow DB2 to release any
  EDM POOL space accumulated for that stored proc package,
  thus preventing the EDM POOL FULL.
  Via this apar DB2 code will be corrected to not create in the
  EDM POOL so many copies of the static ALLOCATE CURSOR stmt that
  is contained in a stored procedure.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 for z/OS users of stored procedures    *
  *                 that contain static statements and are       *
  *                 bound RELEASE ( DEALLOCATE )                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: After many invocations of a stored      *
  *                      procedure that contained a static       *
  *                      ALLOCATE CURSOR statement, an EDM POOL  *
  *                      FULL condition occurred --              *
  *                       DSNT500I DSNGEPLC RESOURCE UNAVAILABLE *
  *                                REASON 00C90089               *
  *                                TYPE 00000600                 *
  *                                NAME EDM POOL SPACE           *
  *                      and                                     *
  *                        SQLCODE904 returned to application    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  User repeatedly invoked a nested DB2 stored procedure scenario
  where calling stored proc was bound RELEASE ( DEALLOCATE ) and
  used static ALLOCATE CURSOR stmt to access result set from inner
  stored procedure.  Eventually an EDM POOL FULL condition
  occurred withg the following operator console message:
    DSNT500I DSNGEPLC RESOURCE UNAVAILABLE
             REASON 00C90089
             TYPE 00000600
             NAME EDM POOL SPACE
                                                                 .
  and SQLCODE -904 with the above REASON code and resource TYPE
  was returned to the application.
                                                                 .
  The RELEASE(DEALLOCATE) usage, as expected and designed,
  prevented a COMMIT outside of the stored procedures from freeing
  the EDM POOL storage.  However, DB2 Development examined the EDM
  POOL FULL dump and observed that most of the EDM POOL space for
  the stored procedure package bound RELEASE(DEALLOCATE) was
  occupied by an exceedingly high number of the identical static
  ALLOCATE CURSOR statement.  DB2 Dev concluded that DB2 should
  not have loaded so many copies of that ALLOCATE CURSOR stmt for
  this single package in the EDM POOL.
                                                                 .
  That DB2 error combined with usage of bind option REL(DEALLOC)
  (which prevented DB2 from releasing the EDM POOL space at
  COMMIT) caused the reported EDM POOL FULL condition.
  The DB2 error was that DB2 did not correctly invoke EDM to free
  the EDM POOL storage for the ALLOCATE CURSOR statement when it
  was used within a stored procedure.  This specific EDM POOL free
  process is only intended for repeated invocations of a stored
  procedures or UDF.
                                                                 .
  This could also occur for other static SQL statements that
  are "parsed only" and not completely bound at BIND PACKAGE of
  the stored procedure package. This special category of "parsed
  only" static SQL statements have the following values for
  column STATUS in catalog table SYSIBM.SYSPACKSTMT after a BIND
  or REBIND PACKAGE:
     A , B , D , F , G , H , M, and some cases of value K or blank
                                                                .
  The Static ALLOCATE CURSOR stmt and static SQL that reference
  declared global temporary tables (DGTTs, qualified by SESSION )
  are in this category of "parsed only" static stmts.
  Regular static SQL statements do not fall into this category and
  would not have the problem addressed by this apar.
                                                                 .
  If RELEASE(COMMIT) had been used instead of REL(DEALLOC) with
  the COMMIT issued outside of the stored procs, then no EDM POOL
  FULL condition would have occurred.
                                                                 .
  

Problem conclusion

• DB2 code was changed to correctly invoke EDM to free these
  special forms of "parsed only" static SQL statements such as
  ALLOCATE CURSOR and DGTT stmts when they are used within
  stored procedures.
                                                                .
  Additional search keywords: SQLSP SQLSTOREDPROC SQLSTORAGE
                              RC00C90089 EXCESSIVE GROWTH
                              PROLIFERATION
  

Temporary fix

• AM07842
  

Comments

APAR Information

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

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

  UK55956

Modules/Macros

• DSNXECLF
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK55956

     UP10/04/24 P F004

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.