DB2 FOR LUW / DB2FORLUW / 970 - 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                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Apply v9.7 Fixpack 7 to the DB2 database server.             * 
****************************************************************

Do not run the statement using the REOPT option.

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
 

First fixed in v9.7 Fixpack 7.  This is a server side fix.

not known / see Local fix

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

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

06.06.2012
18.10.2012
07.12.2012

9.7.FP7