PK58925: INCORRECT BEHAVIOR WHEN CONDITION OF SQL/PL IF STATEMENT CONTAINS TABLE/UDF

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect behavior when condition of sql/pl IF statement
  contains TABLE/UDF.            (defect 105723)
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R910 users of native SQL procedures  *
  ****************************************************************
  * PROBLEM DESCRIPTION: In some cases during execution of a     *
  *                      native SQL procedure, processing would  *
  *                      not continue as expected when an        *
  *                      exception condition is raised and       *
  *                      successfully handled by a CONTINUE      *
  *                      HANDLER.  Processing will not continue  *
  *                      at the statement that immediately       *
  *                      follows the statement that activated    *
  *                      the handler, resulting in unexpected    *
  *                      procedure behavior.  This occurred for  *
  *                      the following SQL control statements,   *
  *                      when search-condition or expression     *
  *                      contains either a scalar fullselect     *
  *                      or a reference to a function:           *
  *                                                              *
  *                      (1) IF search-condition THEN            *
  *                          .....                               *
  *                          END IF;                             *
  *                                                              *
  *                      (2) IF search-condition THEN            *
  *                          ....                                *
  *                          ELSEIF search-condition THEN        *
  *                          ....                                *
  *                          END IF;                             *
  *                                                              *
  *                      (3) CASE expression                     *
  *                          WHEN expression THEN                *
  *                          ....                                *
  *                          END CASE;                           *
  *                                                              *
  *                      (4) CASE WHEN search-condition THEN     *
  *                          ....                                *
  *                          END CASE;                           *
  *                                                              *
  *                      (5) WHILE search-condition DO           *
  *                          ....                                *
  *                          END WHILE;                          *
  *                                                              *
  *                      (6) REPEAT ....                         *
  *                          UNTIL search-condition              *
  *                          END REPEAT;                         *
  *                                                              *
  *                      (7) RETURN expression                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In some cases during execution of a native SQL procedure,
  processing would not continue as expected when an exception
  condition is raised and successfully handled by a CONTINUE
  HANDLER. Processing will not continue at the statement that
  immediately follows the statement that activated the handler,
  resulting in unexpected procedure behavior.  This occurred for
  the following SQL control statements, when search-condition
  or expression contains either a scalar fullselect or a reference
  to a function:
  
  (1) IF search-condition THEN .... END IF;
  
  (2) IF search-condition THEN ....
      ELSEIF search-condition THEN .... END IF;
  
  (3) CASE expression WHEN expression THEN .... END CASE;
  
  (4) CASE WHEN search-condition THEN .... END CASE;
  
  (5) WHILE search-condition DO .... END WHILE;
  
  (6) REPEAT .... UNTIL search-condition END REPEAT;
  
  (7) RETURN expression;
  
  EXAMPLE
  =======
  
  (1) CREATE PROCEDURE PROC1()
      LANGUAGE SQL
      BEGIN
        DECLARE VAR INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
          SET VAR = 1;
        END;
  
        IF (SELECT 1 FROM TABLE_NOT_EXIST) = 1 THEN
          SET VAR  = 0;
        ELSE
          SET VAR = 2;
        END IF;
  
        SET VAR = 3;
      END;
  

Problem conclusion

• DB2 was fixed so that after a CONTINUE HANDLER defined within
  a native SQL procedure handles an error condition successfully,
  it will continue at a correct statement in the procedure.
  
  Additional Keywords: SQLSP SQLNATIVESQLPL SQLINCORR INCORROUT
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK33862

Modules/Macros

• DSNXOYCS DSNXOYLK DSNXOYP0 DSNXOYTR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK33862

     UP08/03/06 P F803

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.