DB2 FOR LUW / DB2FORLUW / 970 - DB2

When a function with a row return type is used as the source 
expression for an INSERT or SET 
statement, the function may be executed once per row field. 
This will occur when: 
 
- the row is used as a source value in an INSERT statement, or 
- the row is used as a source value in a SET statement, and 
either the source or target has an anchored row 
data type. 
 
This may cause unexpected performance problems, if the function 
executed multiple times is complex.  It could also result in 
wrong results, if for example the function executed multiple 
times modifies a global variable, the modification to the global 
variable would also occur multiple times. 
 
For example, 
 
create table t1 (c1 int, c2 int); 
DB20000I  The SQL command completed successfully. 
 
create type row1 as row (f1 int, f2 int); 
DB20000I  The SQL command completed successfully. 
 
create function foo (a int) returns anchor row t1 
begin 
 declare v1 row1;-- 
 set v1 = (a, a + 1);-- 
 set gv1 = gv1 + 1;-- 
 return v1;-- 
end 
DB20000I  The SQL command completed successfully. 
 
create variable gv1 int default 0; 
DB20000I  The SQL command completed successfully. 
 
create variable gvRow1 row1; 
 
set gvRow1 = foo(1) 
DB20000I  The SQL command completed successfully. 
 
values gv1 
 
1 
----------- 
          2 
 
  1 record(s) selected. 
 
The global variable gv1 was incremented twice even though the 
function was only specified in the SET statement once.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of DB2 LUW version 9.7                             * 
**************************************************************** 
* 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       :

28.02.2012
06.06.2012
06.06.2012

9.7.FP6