DB2 FOR LUW / DB2FORLUW / 970 - DB2

Memory blocks for cursor variables might remain if a compound 
statement in a compiled SQL routine terminates early, in 
response to a condition handler or unhandled exception 
condition. 
 
The following example shows a SIGNAL statement that exits a 
procedure after using a local cursor variable.  Each time the 
procedure is invoked, memory blocks are leaked.  The following 
steps illustrate the issue. 
 
1. Deploy procedures like as below: 
 
db2 connect to sample 
db2 -td@ -vf p1.sql 
--------------------- 
CREATE OR REPLACE PROCEDURE PROC2() 
MODIFIES SQL DATA 
LANGUAGE SQL 
BEGIN 
  DECLARE CUR2 CURSOR; 
  SET CUR2 = CURSOR WITH HOLD FOR VALUES CURRENT TIMESTAMP; 
  OPEN CUR2; 
  CLOSE CUR2; 
  SIGNAL SQLSTATE '70100' SET MESSAGE_TEXT = 'Err'; 
END@ 
 
CREATE OR REPLACE PROCEDURE PROC1() 
MODIFIES SQL DATA 
LANGUAGE SQL 
BEGIN 
  DECLARE SQLERRM VARCHAR(256); 
  DECLARE EXIT HANDLER FOR SQLSTATE VALUE '70100' 
  BEGIN 
    GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT; 
  END; 
  CALL PROC2(); 
END@ 
--------------------- 
 
2. Call the procedure several times, then take memory block 
statistics for the application. 
 
for i in `seq 1 250` 
> do 
> db2 "call PROC1" > /dev/null 
> done 
 
db2pd -db sample -memblock appl 1 sort 
 
Memory blocks sorted by size for apph pool: 
PoolID  PoolName  TotalSize(Bytes)  TotalCount LOC   File 
1       apph      54000             250        1159  1043897861 
1       apph      30000             250        11229 4262186386 
1       apph      16000             250        1216  1043897861 
<snip>

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who use cursor variables.                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 V9.7 FixPack 11 or later.              * 
****************************************************************

Please disconnect the application from the database to free up 
application heap.

This problem was first fixed in DB2 V9.7 FixPack 11.

not known / see Local fix

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

29.01.2015
08.10.2015
08.10.2015

9.7.FP11