DB2 FOR LUW / DB2FORLUW / A10 - DB2

When using array of variable length data type in a SQL Stored 
Procedure and assigning different length element to the same 
slot many times you may see memory consumption grow and 
performance worsen. This can happen with both local variables 
and with global variables, but is more 
common with global variables. 
 
Here is some sample code that can be used to demonstrate the 
problem 
 
create package M1 
as 
  TYPE GCHAR IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30); 
  G_CHAR GCHAR; 
end; 
 
create procedure P1(X in number) 
as 
  i number; 
begin 
  FOR I IN 1..X 
  LOOP 
      M1.G_CHAR('1') := NULL; 
      M1.G_CHAR('2') := NULL; 
      M1.G_CHAR('3') := NULL; 
      M1.G_CHAR('4') := NULL; 
      M1.G_CHAR('1') := cast(i as varchar2(30)); 
  END LOOP; 
end; 
 
If you then call this sample procedure 
 
call P1(3000); 
 
declare 
  V varchar2(30); 
begin 
  V := M1.G_CHAR('1'); 
  dbms_output.put_line(V); 
end; 
 
call P1(3000); 
 
The second call will be significantly slower.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of array of variable length datatype               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 FP5 or higher.                           * 
****************************************************************

Problem first fixed in DB2 10.1 FP5

not known / see Local fix

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

24.04.2015
16.07.2015
16.07.2015