DB2 FOR LUW / DB2FORLUW / 970 - DB2

A function that returns a row result type and has output 
parameters can produce unpredictable results.  The assignment to 
the variables representing the output parameters and return 
value are shifted such that: 
 
output parameter 1 is assigned to the variable for the return 
value 
output parameter 2 is assigned to the variable for output 
parameter 1 
... 
output parameter N is assigned to the variable for output 
parameter N-1 
the return value is assigned to the variable for output 
parameter 1 
 
The problem occurs when assigning a function with a row result 
type to a row variable using the SET or VALUES INTO statements. 
Either the source or target (or both) must be an anchored row 
data type, and the source function must have one or more output 
parameters. 
 
An error will likely be reported as a result of these mismatched 
assignments, for example, SQL0303N.  It is also possible no 
error is reported but the function invocation produces wrong 
results.  This symptom is demonstrated in the example below. 
 
create table t1 (c1 varchar(20)) 
DB20000I  The SQL command completed successfully. 
 
create table result (type varchar(20), text varchar(20)) 
DB20000I  The SQL command completed successfully. 
 
create type row1 as row anchor row t1 
DB20000I  The SQL command completed successfully. 
 
create function foo(out a varchar(20)) returns anchor row t1 
begin 
 declare returnVal anchor row t1; 
 set a = 'output value'; 
 set returnVal.c1 = 'return value'; 
 return returnVal; 
end 
DB20000I  The SQL command completed successfully. 
 
begin 
 declare v1 anchor row t1; 
 declare v2 varchar(20); 
 set v1 = foo(v2); 
 insert into result values ('output', v2), ('result', v1.c1); 
end 
DB20000I  The SQL command completed successfully. 
 
select * from result 
 
TYPE                 TEXT 
-------------------- -------------------- 
output               return value 
result               output value 
 
  2 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW Version 9.7 Fix Pack 6                    * 
****************************************************************

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
 

Fixed in DB2 LUW Version 9.7 Fix Pack 6

not known / see Local fix

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

12.03.2012
06.06.2012
06.06.2012

9.7.FP6