LI74527: TRAP WHILE COMPILING AN SQL PROCEDURE WITH A SET STATEMENT CONTAINING EXISTS

DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• DB2 may terminate abnormally during compilation of an SQL
  procedure.  In the stack trace for the trap, you will see a
  reference to routine sqlnq_clean_psm_boxes.  This problem may
  occur if the procedure contains a SET statement with an EXISTS
  predicate on the right-hand side.
  
  For example:
    SET N = CASE WHEN EXISTS (SELECT C1 FROM T1 WHERE C2=42) THEN
  1 ELSE 0 END;
  

Local fix

• As a workaround, restructure the affected statement to move the
  existence test to an IF statement.  For example, instead of:
  
    SET N = CASE WHEN EXISTS (SELECT C1 FROM T1 WHERE C2=42) THEN
  1 ELSE 0 END;
  
  use an IF statement for the conditional logic:
  
    IF EXISTS (SELECT C1 FROM T1 WHERE C2=42) THEN
      SET N = 1;
    ELSE
      SET N = 0;
    END IF;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * NONE                                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * DB2 may terminate abnormally during compilation of an SQL    *
  *                                                              *
  * procedure.  In the stack trace for the trap, you will see a  *
  *                                                              *
  * reference to routine sqlnq_clean_psm_boxes.  This problem    *
  * may                                                          *
  * occur if the procedure contains a SET statement with an      *
  * EXISTS                                                       *
  * predicate on the right-hand side.                            *
  *                                                              *
  *                                                              *
  *                                                              *
  * For example:                                                 *
  *                                                              *
  *   SET N = CASE WHEN EXISTS (SELECT C1 FROM T1 WHERE C2=42)   *
  * THEN                                                         *
  * 1 ELSE 0 END;                                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * NONE                                                         *
  ****************************************************************
  

Problem conclusion

• As a workaround, restructure the affected statement to move the
  
  existence test to an IF statement.  For example, instead of:
  
  
    SET N = CASE WHEN EXISTS (SELECT C1 FROM T1 WHERE C2=42) THEN
  1 ELSE 0 END;
  
  use an IF statement for the conditional logic:
  
    IF EXISTS (SELECT C1 FROM T1 WHERE C2=42) THEN
      SET N = 1;
    ELSE
      SET N = 0;
    END IF;
  

Temporary fix

Comments

APAR Information

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

  LI74495

• 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

• R910 PSN

     UP