PK88917: STORED PROCEDURE USING HUGE AMOUNTS OF VIRTUAL STORAGE IN DBM1 DUE TO LEAK OF SP RESULT SET BLOCKS RSL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Stored procedure using huge amounts of virtual storage
  in DBM1 due to leak of sp result set blocks RSL .
  .
  In the reported case a stored procedure SP1 was doing a
  connect to a remote subsystem and then calling a second stored
  procedure SP2. Both stored procs had a cursor for result set
  with attribute WITH HOLD .
  
  DB2STGLK/K
  

Local fix

• Remove WITH HOLD cursor attribute on the
  remote stored procedure SP2 to help alleviate the
  DBM1 storage usage .
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of remote stored procedures    *
  *                 on DB2 for z/OS v8 or DB2 9 (for z/OS) that  *
  *                 open result set cursors declared WITH HOLD.  *
  *                                                              *
  *                                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: DBM1 storage shortage related abends.   *
  *                      Common out of storage abend and reason  *
  *                      codes:                                  *
  *                           ABEND04E / AB04E  with             *
  *                           RC00E20003 / RC00E20004 /          *
  *                           RC00E2000B / RC00E2000C            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In the reported scenario, customer's batch job
  at a V8 requester repeatedly invokes a remote
  stored procedure at a V8 server following an
  explicit CONNECT statement. The remote stored
  procedure opens result set cursors that are
  declared WITH HOLD. Here is a simplified
  version of the customer's scenario:
  
  At DB2 requester AR:
   main(){
    loop-
       CONNECT to DB2 server AS
       CALL stored procedure sp
       ASSOCIATE LOCATOR for sp
       ALLOCATE CURSOR
       FETCH
       CLOSE
       COMMIT
       }
  
  At DB2 server AS:
   sp(){
       OPEN result set WITH HOLD
       }
  
  DB2 requester abends due to shortage of virtual
  storage in DBM1 address space. Visual examination
  of the dump storage mapping of the AGL
  ( Agent Local ) pool showed excessive
  number of blocks with eyecatcher 'RSL'.
  
  DB2 allocates an internal control block in DBM1
  at the requester to represent every remote stored
  procedure. These control blocks should normally be
  freed at COMMIT time if there are no outstanding
  result sets left open by the remote stored procedure.
  
  In the customer's case, although the calling
  program at the requester closes the WITH HOLD
  result sets returned by the remote stored
  procedure, COMMIT did not free the stored
  procedure control block storage.  The consumption
  of DBM1 virtual storage by thousands of
  invocations to the remote stored procedure
  causes shortage of storage, causing DB2 requester
  to abend.
  

Problem conclusion

• DB2 code is modified to free the internal control
  block storage representing remote stored procedures at
  COMMIT when all WITH HOLD result sets opened
  by the stored procedure have been closed.
  
  
  RELATED KEYWORDS: SQLSP SQLSTOREDPROC SQLSTORAGE
                    NFM RC00C90089 ABEND04E AB04E 00E20003
                    00E20004 00E2000B 00E2000C GROWTH LEAK
                    EXCESSIVE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK49539 UK49540

Modules/Macros

• DSNXEPM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK49539

     UP09/09/09 P F909

• R910 PSY UK49540

     UP09/09/09 P F909

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.