IBM Support

LI74238: INCORRECT RESULTS IN AN SQL STORED PROCEDURE CONTAINING A BRANCH TO A LABELLED SET STATEMENT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When all of the following conditions are present, you may
    observe incorrect results during execution of an SQL procedure:
    
    - The procedure contains a SET statement with a statement label;
    - The labelled SET statement is immediately followed by one or
      more additional unlabelled SET statements.
    - The procedure uses a GOTO statement to branch to the labelled
      statement.
    
    Some of the SET statements in the sequence may not be executed.
    
    Example:
    
        GOTO L1;
    
    -- ...
    
    L1: SET S1 = 'A = ' || CHAR(A);
        SET X = 1;
        SET Y = 20;
        SET S2 = 'Z = ' || CHAR(Z);
    
    In this particular example, the SET statements assigning values
    to X and Y are not executed.  Any non-SET statements following
    this sequence would be executed normally.
    
    This problem does not affect all possible sequences of SET
    statements that satisfy the APAR conditions.  Some sequences
    will execute correctly.  In the general case, it is difficult to
    predict which statements may be skipped when the problem occurs.
    However, the behaviour of the stored procedure will be
    consistent each time it is executed.   That is, if certain SET
    statements are skipped when the procedure is executed, the same
    statements will be skipped each time.
    
    
    The problem can be reproduced as follows:
    
    $ db2 -td@ -vf proc1.txt
    drop procedure proc1
    DB20000I  The SQL command completed successfully.
    
    create procedure proc1
    (
     out param1 character(4),
     out param2 character(4),
     out param3 character(4)
    )
    
    p1: begin
       declare var1 character(4) default 'var1';
       declare var2 character(4) default 'var2';
       declare var3 character(4) default 'var3';
    
       goto exit_rtn;
    
       exit_rtn:
    
       set param1 = char(var1,18);
       set param2 = char(var2,18);
       set param3 = var3;
    end p1
    DB20000I  The SQL command completed successfully.
    
    $ db2 "call proc1(?,?,?)"
    
      Value of output parameters
      --------------------------
      Parameter Name  : PARAM1
      Parameter Value : var1
    
      Parameter Name  : PARAM2
      Parameter Value : var2
    
      Parameter Name  : PARAM3
      Parameter Value : -
    
      Return Status = 0
    

Local fix

  • Attach the statement label to a non-SET statement and recompile
    the stored procedure.  For example:
    
    L1: BEGIN END;
        SET S1 = 'A = ' || CHAR(A);
    

Problem summary

  • Users affected:
    Users of SQL stored procedures with DB2 on Linux, UNIX and
    Windows
    
    
    Problem Description:
    When all of the following conditions are present, you may
    observe incorrect results during execution of an SQL procedure:
    
    - The procedure contains a SET statement with a statement label.
    - The labelled SET statement is immediately followed by one or
      more additional unlabelled SET statements.
    - The procedure uses a GOTO statement to branch to the labelled
      statement.
    
    Some of the SET statements in the sequence may not be executed.
    
    Example:
    
        GOTO L1;
    
    -- ...
    
    L1: SET S1 = 'A = ' || CHAR(A);
        SET X = 1;
        SET Y = 20;
        SET S2 = 'Z = ' || CHAR(Z);
    
    In this particular example, the SET statements assigning values
    to X and Y are not executed.  Any non-SET statements following
    this sequence would be executed normally.
    
    This problem does not affect all possible sequences of SET
    statements that satisfy the APAR conditions.  Some sequences
    will execute correctly.  In the general case, it is difficult to
    predict which statements may be skipped when the problem occurs.
    However, the behaviour of the stored procedure will be
    consistent each time it is executed.   That is, if certain SET
    statements are skipped when the procedure is executed, the same
    statements will be skipped each time.
    
    
    Problem Summary:
    The problem can be reproduced as follows:
    
    $ db2 -td@ -vf proc1.txt
    drop procedure proc1
    DB20000I  The SQL command completed successfully.
    
    create procedure proc1
    (
     out param1 character(4),
     out param2 character(4),
     out param3 character(4)
    )
    
    p1: begin
       declare var1 character(4) default 'var1';
       declare var2 character(4) default 'var2';
       declare var3 character(4) default 'var3';
    
       goto exit_rtn;
    
       exit_rtn:
    
       set param1 = char(var1,18);
       set param2 = char(var2,18);
       set param3 = var3;
    end p1
    DB20000I  The SQL command completed successfully.
    
    $ db2 "call proc1(?,?,?)"
    
      Value of output parameters
      --------------------------
      Parameter Name  : PARAM1
      Parameter Value : var1
    
      Parameter Name  : PARAM2
      Parameter Value : var2
    
      Parameter Name  : PARAM3
      Parameter Value : -
    
      Return Status = 0
    

Problem conclusion

  • Problem was first fixed in Version 9.5, Fix Pack 4
    

Temporary fix

  • Attach the statement label to a non-SET statement and recompile
    the stored procedure.  For example:
    
    L1: BEGIN END;
        SET S1 = 'A = ' || CHAR(A);
    

Comments

APAR Information

  • APAR number

    LI74238

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-06

  • Closed date

    2009-06-02

  • Last modified date

    2009-06-02

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

    LI74236

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 June 2009