Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC68699 Status: Closed

MEMORY LEAK WHILE CALLING NESTED STORED PROCEDURES

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* 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                                      * 
****************************************************************
Local Fix:
if we instead call sp3 from sp1 the problem does not occur  - 
memory consumption is constant.
available fix packs:
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

Solution
Upgrade to DB2 v9.7 Fp3
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC69628 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
18.05.2010
29.09.2010
29.09.2010
Problem solved at the following versions (IBM BugInfos)
9.7.FP3
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.3 FixList
9.7.0.3 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.