DB2 FOR LUW / DB2FORLUW / A10 - DB2

A statement that contains an array variable, might produce 
incorrect output when run under REOPT ONCE or REOPT ALWAYS.  The 
array variable will be treated as null in the statement. 
 
For this problem to occur, the statement must contain an array 
variable, and one other variable that is not a LOB, ARRAY, ROW, 
or CURSOR type. 
 
For example, 
 
call sysproc.set_routine_opts('reopt always') 
 
  Return Status = 0 
 
create type myarray as integer array[10] 
DB20000I  The SQL command completed successfully. 
 
create function foo () returns integer 
begin 
   declare v1 myarray;-- 
   declare index integer;-- 
   declare returnVal integer default 0;-- 
 
   set v1 = ARRAY[1, 3, 5];-- 
   set index = 2;-- 
 
   if v1[index] > index  then 
        set returnVal = 1;-- 
   end if;-- 
 
   return returnVal;-- 
end 
DB20000I  The SQL command completed successfully. 
 
values foo() 
 
1 
----------- 
          0 
 
  1 record(s) selected. 
 
A statement containing ARRAY variables, might produce incorrect 
results. 
 
 
SQL0901N is returned with REOPT enabled 
(REOPT=ALWAYS/ONCE) for the cases below: 
 
(1) A statement containing a ROW variable. 
 
For example, 
 
call sysproc.set_routine_opts('reopt always') 
 
  Return Status = 0 
 
create table t1 (c1 int, c2 int) 
DB20000I  The SQL command completed successfully. 
 
create type myrow as row anchor row t1 
DB20000I  The SQL command completed successfully. 
 
begin 
 declare v1 myrow;-- 
 
 set v1 = (1, 2);-- 
 
 insert into t1 values v1;-- 
end 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing, it returned: 
 
SQL0901N  The SQL statement or command failed because of a 
database system 
error. (Reason "invalid qnc assigment".)  SQLSTATE=58004 
 
 
(2) A statement containing ROW or CURSOR variable: 
 
SQL0901N  The SQL statement or command failed because of a 
database system error. (Reason "invalid qnc assigment".) 
SQLSTATE=58004 
 
SQL0901N  The SQL statement or command failed because of a 
database system error. (Reason "cannot find cursor info in 
ERT".)  SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of DB2 LUW versions 9.7 and 10.1                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW version 10.1 fix pack 1.                  * 
****************************************************************

Do not run the statement using the REOPT option.

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

See Local Fix

Fix Release: DB2 LUW version 10.1 fix pack 2 
Fix Commitment Level: 999

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

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

29.05.2012
13.09.2012
13.09.2012