IBM Support

IC72985: IF OR CASE STATEMENT MAY FAIL WITH SQL0206N WHEN IT CONTAINS A SINGLE SET STATEMENT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An IF or CASE statement may fail with SQL0206N when each clause
    contains a single SET statement with the same target (left-hand
    side) variable, and the right-hand side of one or more of the
    SET statements references a compiled SQL function.
    For example, the following UDF fails with SQL0206N when it's
    used in the following IF statement:
    ========
    set serveroutput on %
    
    create or replace function test (inout p_msg varchar(256))
    returns varchar(256)
    begin
      declare v_msg varchar(256);
    
      set v_msg = 'Hello';
      return v_msg;
    end %
    
    begin
      declare v_msg1 varchar(256);
      declare v_msg2 varchar(256);
    
      set v_msg1 = 'ABCDE';
      call dbms_output.put_line('v_msg1 = ' || v_msg1);
      if (v_msg1 is null) or (v_msg1 = 'NOTHING') then
        set v_msg2 = 'EMPTY';
      else
    --  set v_msg2 = 'DUMMY';
        set v_msg2 = test(v_msg1);
      end if;
      call dbms_output.put_line ('v_msg1 = ' || v_msg1);
      call dbms_output.put_line ('v_msg2 = ' || v_msg2);
    end %
    ========
    

Local fix

  • Adding another SET statement which doesn't refer to a compiled
    SQL function will avoid the problem. The above example will work
    fine by enabling the comment line "set v_msg2 = 'DUMMY';".
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 UDB Version 9.7                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * An IF or CASE statement may fail with SQL0206N when each     *
    * clause contains a single SET statement with the same target  *
    * (left-hand                                                   *
    * side) variable, and the right-hand side of one or more of    *
    * the SET statements references a compiled SQL function.       *
    * For example, the following UDF fails with SQL0206N when it's *
    * used in the following IF statement:                          *
    * ========                                                     *
    * set serveroutput on %                                        *
    *                                                              *
    * create or replace function test (inout p_msg varchar(256))   *
    * returns varchar(256)                                         *
    * begin                                                        *
    *   declare v_msg varchar(256);                                *
    *   set v_msg = 'Hello';                                       *
    *   return v_msg;                                              *
    * end %                                                        *
    *                                                              *
    * begin                                                        *
    *   declare v_msg1 varchar(256);                               *
    *   declare v_msg2 varchar(256);                               *
    *                                                              *
    *   set v_msg1 = 'ABCDE';                                      *
    *   call dbms_output.put_line('v_msg1 = ' || v_msg1);          *
    *   if (v_msg1 is null) or (v_msg1 = 'NOTHING') then           *
    *     set v_msg2 = 'EMPTY';                                    *
    *   else                                                       *
    * --  set v_msg2 = 'DUMMY';                                    *
    *     set v_msg2 = test(v_msg1);                               *
    *   end if;                                                    *
    *   call dbms_output.put_line ('v_msg1 = ' || v_msg1);         *
    *   call dbms_output.put_line ('v_msg2 = ' || v_msg2);         *
    * end %                                                        *
    * ========                                                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Version 9.7 FixPack 4.                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.7 FixPack 4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72985

  • 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

    2010-12-02

  • Closed date

    2011-05-05

  • Last modified date

    2011-05-05

  • 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 #: IC72985

Modified date: 05 May 2011