PK68249: SQLCODE104 CREATE TRIGGER WITH BEGIN ATOMIC/END, CASE/END STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When we CREATE TRIGGER with BEGIN ATOMIC/END, CASE/END
  statement, DB2 V910 will issue following error msg:
  
  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "END". SOME
  SYMBOLS THAT MIGHT BE LEGAL ARE: ;
  DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNHLEX SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 21  0  0  -1  622  502 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'00000015'  X'00000000'  X'00000000'
  X'FFFFFFFF'  X'0000026E'  X'000001F6' SQL DIAGNOSTIC
  INFORMATION
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the CREATE TRIGGER statement on     *
  *                 DB2 V9.1 for z/OS.                           *
  ****************************************************************
  * PROBLEM DESCRIPTION: When the triggered-SQL-statements       *
  *                      inside an SQL-trigger-body for the      *
  *                      CREATE TRIGGER statement contain        *
  *                      CASE expressions and quoted strings,    *
  *                      the DB2 Parser issues sqlcode104 by     *
  *                      mistake because a flag for the quoted   *
  *                      string process is not reset correctly.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Here is an example of the CREATE TRIGGER statement that yeilds
  incorrect SQLCODE104 from DB2 Parser.
  
  CREATE TRIGGER TRIG1 AFTER INSERT ON T.TABLE5
  REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
      INSERT INTO T.TABLE1
        SELECT
        CASE
          WHEN N.COL1 <> ''
          THEN INTEGER(N.COL1)
          ELSE 0
        END , ' '
        FROM SYSIBM.SYSDUMMY1
        WHERE NOT EXISTS (
        SELECT * FROM T.TABLE1
        WHERE COL2 =
          CASE
            WHEN N.COL1 <> ''
            THEN INTEGER(N.COL1)
            ELSE 0
          END ) ;
      INSERT INTO T.TABLE2 VALUES
      (N.COL3
       ,CASE
          WHEN N.COL4 <> ''
          THEN INTEGER(N.COL4)
          ELSE 0
        END
       ,CASE
          WHEN N.COL5 <> ''
          THEN INTEGER(N.COL5)
          ELSE 0
       END
       ,N.COL6) ;
    END
  
  where both INSERT statements contain string-constants and
  CASE expressions. DB2 Parser does not initialize a flag for the
  quoted string correctly,  and therefore issues sqlcode104 by
  mistake.
  
  *** TEP3E15   SQLERROR ON CREATE     COMMAND, PREPARE  FUNCTION
      TEP3I05   RESULT OF SQL STATEMENT:
       DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "END".
                SOME SYMBOLS  THAT MIGHT BE LEGAL ARE: ;
       DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
       DSNT415I SQLERRP  = DSNHLEX SQL PROCEDURE DETECTING ERROR
       DSNT416I SQLERRD  = 21  0  0  -1  418  502
                SQL DIAGNOSTIC INFORMATION
       DSNT416I SQLERRD  = X'00000015'  X'00000000'  X'00000000'
                X'FFFFFFFF'  X'000001A2'  X'000001F6'
                SQL DIAGNOSTIC INFORMATION
      TEP3I55   RETCODE  = 8
  

Problem conclusion

• DB2 parser code is fixed so that the incorrect sqlcode104 is no
  longer issued.
  

Temporary fix

Comments

APAR Information

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

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

  UK38192

Modules/Macros

• DSNHLEX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK38192

     UP08/08/01 P F807

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.