DB2 FOR LUW / DB2FORLUW / 970 - DB2

we see an application failing due to unavailable memory. 
Allocating more 
memory does not help - the application consumes everything and 
fails. 
 
 
SQL/PL stored procedure 1 calls SQL/PL sp 2 which calls SQL/PL 
sp 3. 
sp3 opens a cursor fetches a row and returns to sp2 which then 
returns 
to sp1. sp1 iterates several hundred thousand times. 
 
Monitoring we see memory consumed by the agent increasing 
continually. 
 
 
However, if we instead call sp3 from sp1 the problem does not 
occur  - 
memory consumption is constant. 
 
Also, changing the cursor to a select ... fetch first 1 row 
avoids the 
problem. 
 
Steps to reproduce the issue : 
 
create procedure three 
begin 
  declare N integer; 
 
  declare C1 cursor for 
    select 42 from sysibm.sysdummy1; 
 
  open C1; 
  fetch C1 into N; 
  close C1; 
end % 
 
create procedure two(in iterations integer) 
begin 
  declare i integer default 0; 
 
  while i < iterations do 
    call three; 
    set i = i + 1; 
  end while; 
end % 
 
create procedure one 
begin 
  call two(500000); 
end % 
 
connect reset % 
terminate % 
 
If you 'db2 call one', you get the memory leak.

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 v9.7 on all platform                                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Memory leak when calling nested stored procedures            * 
*                                                              * 
* When SQL/PL Stored Procedure 1 calls SQL/PL Stored Procedure * 
* 2 which calls SQL/PL Stored Procedures.                      * 
*                                                              * 
* If Stored procedures 3  opens a cursor which fetches a row   * 
* and returns to Stored Procedure 2,  which then               * 
* returns to Stored procedure 3, a small memory leak will      * 
* occurs.                                                      * 
*                                                              * 
* Memory consumed by the agent will increase continuously if   * 
* the Stored procedures are repeated being executed            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.7 Fp3                                      * 
****************************************************************

if we instead call sp3 from sp1 the problem does not occur  - 
memory consumption is constant.

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Upgrade to DB2 v9.7 Fp3

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC69628 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

18.05.2010
29.09.2010
29.09.2010

9.7.FP3