IBM Support

IC76616: UNQUALIFIED ROW FIELD REFERENCE CAN RESOLVE TO VARIABLE IN PARENT SCOPE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error 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
    

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.
    

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.  *
    ****************************************************************
    

Problem conclusion

Temporary fix

  • See LOCAL FIX.
    

Comments

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

APAR Information

  • APAR number

    IC76616

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-05-25

  • Closed date

    2011-05-30

  • Last modified date

    2011-05-30

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC76616

Modified date: 30 May 2011