Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: Error Creating Multiple Triggers in One Time Jobstream

    Hope someone can help me out here. I have extracted the DDL via Quest Central and it contains many types of triggers, but when I try to execute the BEFORE UPDATE Trigger portion of the DDL, it fails with an SQL0104N An Unexpected Token "END" was found following "'U' END);". Expected tokens may include : "SIGNAL". SQLSTATE=42601.
    When I run only one Create Trigger statement it runs fine, but I have hundreds of these to do, so this is extremely time consuming. I tried changing the delimiter, but that didn't seem to help with this Trigger.

    I am running Personal Edition 8.2(FP15).

    Sample DDL follows:

    Code:
    SET SCHEMA MARSR;
    
    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
    
    CREATE TRIGGER "MARSR"."BU_ACR"
      NO CASCADE BEFORE
      UPDATE
      ON "MARSR"."ACCOUNT_PRORATION"
      REFERENCING
        OLD AS "O"
        NEW AS "N"
      FOR EACH ROW
      MODE DB2SQL
      WHEN (O.LST_UPDT_TS = N.LST_UPDT_TS
      AND USER <> 'DB2ADMIN'
      AND USER <> 'PRODUSER'
      AND USER <> 'NOTRIG')
      BEGIN ATOMIC
          SET (N.LST_UPDT_EMPL_ID, N.LST_UPDT_TS, N.POST_FLG) = (USER, CURRENT 
            TIMESTAMP,
            CASE
              WHEN O.POST_FLG IN (' ', 'U')
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'U'
                  END
              WHEN O.POST_FLG = 'I'
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'I'
                  END
              WHEN O.POST_FLG = 'D'
                THEN
                  CASE N.POST_FLG
                    WHEN 'U'
                      THEN 'U'
                    ELSE 'D'
                  END
              ELSE 'U'
            END );  <===== Fails here
        END; 
    
    SET SCHEMA MARSR;
    
    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
    
    CREATE TRIGGER "MARSR"."BU_ACS"
      NO CASCADE BEFORE
      UPDATE
      ON "MARSR"."ACCOUNT_SALE"
      REFERENCING
        OLD AS "O"
        NEW AS "N"
      FOR EACH ROW
      MODE DB2SQL
      WHEN (O.LST_UPDT_TS = N.LST_UPDT_TS
      AND USER <> 'DB2ADMIN'
      AND USER <> 'PRODUSER'
      AND USER <> 'NOTRIG')
      BEGIN ATOMIC
          SET (N.LST_UPDT_EMPL_ID, N.LST_UPDT_TS, N.POST_FLG) = (USER, CURRENT 
            TIMESTAMP,
            CASE
              WHEN O.POST_FLG IN (' ', 'U')
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'U'
                  END
              WHEN O.POST_FLG = 'I'
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'I'
                  END
              WHEN O.POST_FLG = 'D'
                THEN
                  CASE N.POST_FLG
                    WHEN 'U'
                      THEN 'U'
                    ELSE 'D'
                  END
              ELSE 'U'
            END );
        END;
    
    SET SCHEMA MARSR;
    
    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
    
    CREATE TRIGGER "MARSR"."BU_ACSD"
      NO CASCADE BEFORE
      UPDATE
      ON "MARSR"."ACCT_SPEC_DIST"
      REFERENCING
        OLD AS "O"
        NEW AS "N"
      FOR EACH ROW
      MODE DB2SQL
      WHEN (O.LST_UPD_TS = N.LST_UPD_TS)
      BEGIN ATOMIC
          SET (N.LST_UPDT_EMPL_ID, N.LST_UPD_tS, N.POST_FLG) = (USER, CURRENT 
            TIMESTAMP,
            CASE
              WHEN O.POST_FLG IN (' ', 'U')
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'U'
                  END
              WHEN O.POST_FLG = 'I'
                THEN
                  CASE N.POST_FLG
                    WHEN 'D'
                      THEN 'D'
                    ELSE 'I'
                  END
              WHEN O.POST_FLG = 'D'
                THEN
                  CASE N.POST_FLG
                    WHEN 'U'
                      THEN 'U'
                    ELSE 'D'
                  END
              ELSE 'U'
            END );
        END;
    Thanks so much for any suggestions!
    Last edited by doncmorris; 08-21-08 at 15:02.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    DB2 cannot understand whether the semicolon there means the end of the CREATE TRIGGER statement, or one of the statements inside the trigger definition. You will need to export DDL statements using some other delimiters, if Quest Central allows it. "@" is commonly used for this purpose. You will then execute the DDL script: db2 -td@ -f myddlscript.sql
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Posts
    2
    Hi n i,
    I changed the delimiter in QC and exported the DDL and executed using the following in a batch job.

    db2cmd /w db2 -td@ -f BUTrig.sql

    BUTRIG.sql:

    #DELIMITER "@"; <=== Inserted by Quest Central(Removed and it worked!!)

    CONNECT TO MARSLOCL user dmorris using xxxxxxx@

    SET SCHEMA MARSR@

    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN"@

    CREATE TRIGGER "MARSR"."BU_CCS"
    NO CASCADE BEFORE
    UPDATE

    It processes the statements after I removed the #DELIMITER "@";

    Thanks,
    Don
    Last edited by doncmorris; 08-21-08 at 16:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •