DB2 FOR LUW / DB2FORLUW / 970 - DB2

When a row field is referenced in a nested scope and is not 
qualified with a scope name, it can resolve to a row variable 
with the same name in a parent scope.  In order for this problem 
to occur all of the following conditions must be met: 
 
- there is an unqualified reference to a field in a row local 
variable, and 
- the row field reference is situated within a nested scope, and 
 
- there exists a local variable in a parent scope with the same 
name as the nested row local variable. 
 
For example, in the procedure below, the SET statement that 
assigns the value 10 to rowvar.c1 will not produce the 
expected result.  The reference to rowvar.c1 will resolve to 
outer.rowvar.c1 in the parent scope.  As a result, both 
inner.rowvar.c1 and outer.rowvar.c1 will receive incorrect data. 
 
create table t1 (c1 int, c2 varchar(20)) 
DB20000I  The SQL command completed successfully. 
 
create procedure proc1 (out p1 int, out p2 int) 
outer: begin 
declare rowvar anchor row t1;-- 
set rowvar.c1 = 5;-- 
 
inner: begin 
  declare rowvar anchor row t1;-- 
  set rowvar.c1 = 10;-- 
  set p1 = inner.rowvar.c1;-- 
  set p2 = outer.rowvar.c1;-- 
end;-- 
end 
DB20000I  The SQL command completed successfully. 
 
call proc1(?,?) 
 
  Value of output parameters 
  -------------------------- 
  Parameter Name  : P1 
  Parameter Value : - 
 
  Parameter Name  : P2 
  Parameter Value : 10 
 
  Return Status = 0

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of DB2 LUW version 9.7 up to and including fix     * 
* pack 4, as well as SQL procedures migrated to later fix      * 
* packs.                                                       * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When a row field is referenced in a nested scope and is not  * 
* qualified with a scope name, it can resolve to a row         * 
* variable with the same name in a parent scope.  As a result  * 
* the SQL procedure can produce incorrect output.              * 
*                                                              * 
* In order for this problem to occur all of the following      * 
* conditions must be met:                                      * 
*                                                              * 
*                                                              * 
* - there is an unqualified reference to a field in a row      * 
* local variable, and                                          * 
*                                                              * 
* - the row field reference is in a nested scope, and          * 
*                                                              * 
* - there exists a local variable in a parent scope with the   * 
* same name as the nested row local variable                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW version 9.7 fix pack 5 and recreate any   * 
* procedures that exhibit the problem described in this APAR.  * 
****************************************************************

If version 9.7 fix pack 5 or later can not be applied, there are 
two possible local fixes. 
 
The first option is to qualify row field references in nested 
scopes.  Here the fix has been applied to the example from the 
ERROR DESCRIPTION. 
 
create table t1 (c1 int, c2 varchar(20)) 
DB20000I  The SQL command completed successfully. 
 
create procedure proc1 (out p1 int, out p2 int) 
outer: begin 
declare rowvar anchor row t1;-- 
set outer.rowvar.c1 = 5;-- 
 
inner: begin 
  declare rowvar anchor row t1;-- 
  set inner.rowvar.c1 = 10;-- 
  set p1 = inner.rowvar.c1;-- 
  set p2 = outer.rowvar.c1;-- 
end;-- 
end 
DB20000I  The SQL command completed successfully. 
 
call proc1(?,?) 
 
  Value of output parameters 
  -------------------------- 
  Parameter Name  : P1 
  Parameter Value : 10 
 
  Parameter Name  : P2 
  Parameter Value : 5 
 
  Return Status = 0 
 
A second local fix is to rename variables in nested scopes so 
that there is no conflict with variables in parent scopes.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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
 

See LOCAL FIX.

Fix release: DB2 LUW version 9.7 fix pack 5 
Fix commitment level: 999

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

25.05.2011
30.05.2011
30.05.2011

9.7.FP5