IBM Support

IC83823: WITH REOPT ENABLED, STATEMENTS CONTAINING ARRAY OR ROW VARIABLES MIGHT PRODUCE INCORRECT OUTPUT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • A statement that contains an array variable, might produce
    incorrect output when run under REOPT ONCE or REOPT ALWAYS.  The
    array variable will be treated as null in the statement.
    
    For this problem to occur, the statement must contain an array
    variable, and one other variable that is not a LOB, ARRAY, ROW,
    or CURSOR type.
    
    For example,
    
    call sysproc.set_routine_opts('reopt always')
    
      Return Status = 0
    
    create type myarray as integer array[10]
    DB20000I  The SQL command completed successfully.
    
    create function foo () returns integer
    begin
       declare v1 myarray;--
       declare index integer;--
       declare returnVal integer default 0;--
    
       set v1 = ARRAY[1, 3, 5];--
       set index = 2;--
    
       if v1[index] > index  then
            set returnVal = 1;--
       end if;--
    
       return returnVal;--
    end
    DB20000I  The SQL command completed successfully.
    
    values foo()
    
    1
    -----------
              0
    
      1 record(s) selected.
    
    A statement containing ARRAY variables, might produce incorrect
    results.
    
    
    SQL0901N is returned with REOPT enabled
    (REOPT=ALWAYS/ONCE) for the cases below:
    
    (1) A statement containing a ROW variable.
    
    For example,
    
    call sysproc.set_routine_opts('reopt always')
    
      Return Status = 0
    
    create table t1 (c1 int, c2 int)
    DB20000I  The SQL command completed successfully.
    
    create type myrow as row anchor row t1
    DB20000I  The SQL command completed successfully.
    
    begin
     declare v1 myrow;--
    
     set v1 = (1, 2);--
    
     insert into t1 values v1;--
    end
    
    DB21034E  The command was processed as an SQL statement because
    it was not a valid Command Line Processor command.  During SQL
    processing, it returned:
    
    SQL0901N  The SQL statement or command failed because of a
    database system
    error. (Reason "invalid qnc assigment".)  SQLSTATE=58004
    
    
    (2) A statement containing ROW or CURSOR variable:
    
    SQL0901N  The SQL statement or command failed because of a
    database system error. (Reason "invalid qnc assigment".)
    SQLSTATE=58004
    
    SQL0901N  The SQL statement or command failed because of a
    database system error. (Reason "cannot find cursor info in
    ERT".)  SQLSTATE=58004
    

Local fix

  • Do not run the statement using the REOPT option.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of DB2 LUW versions 9.7 and 10.1                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW version 10.1 fix pack 1.                  *
    ****************************************************************
    

Problem conclusion

Temporary fix

  • See Local Fix
    

Comments

  • Fix Release: DB2 LUW version 10.1 fix pack 2
    Fix Commitment Level: 999
    

APAR Information

  • APAR number

    IC83823

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-05-29

  • Closed date

    2012-09-13

  • Last modified date

    2012-09-13

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

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

    IC83976 IC83977

Fix information

Applicable component levels

  • R970 PSN

       UP

  • RA10 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]

Document Information

Modified date:
20 September 2021