home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC76616 Status: Closed

UNQUALIFIED ROW FIELD REFERENCE CAN RESOLVE TO VARIABLE IN PARENT SCOPE

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
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.  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
Problem Summary:
**************************************************************** 
* 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.  * 
****************************************************************
Local Fix:
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.
available fix packs:
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

Solution
Workaround
See LOCAL FIX.
Comment
Fix release: DB2 LUW version 9.7 fix pack 5 
Fix commitment level: 999
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
25.05.2011
30.05.2011
30.05.2011
Problem solved at the following versions (IBM BugInfos)
9.7.FP5
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.5 FixList