IBM Support

IC81971: UNPREDICTABLE RESULTS FROM FUNCTION WITH ROW RESULT TYPE AND OUTPUT PARAMETERS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

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

Local fix

Problem summary

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

Problem conclusion

  • Fixed in DB2 LUW Version 9.7 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC81971

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-03-12

  • Closed date

    2012-06-06

  • Last modified date

    2012-06-06

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC81971

Modified date: 06 June 2012