Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    30

    Unanswered: DB2 7.1 for z/OS - Create Trigger problems

    Hi,

    I am new to DB2. Following is the trigger code I tried to execute in z/OS mainframe system.

    CREATE OR REPLACE TRIGGER TUBR01_POC1
    BEFORE INSERT OR UPDATE ON TABLE POC1
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    N.POC_COL2 = CURRENT TIMESTAMP;
    ---------+---------+---------+---------+---------+---------+---------+--------

    I am getting the following error.

    DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD TRIGGER, TOKEN FOR WAS
    EXPECTED
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 19 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000013' X'00000000' SQL DIAGNOSTIC INFORMATION

    Please throw some light on this error. I really appreciate your help

    Thanks
    Santhi

  2. #2
    Join Date
    Feb 2004
    Posts
    24

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hello Santhi,
    you only can create or drop a trigger and there is no replace command avail for trigger. You also can create a update or insert trigger not both at once.

    Hope this will help

    Marc
    Originally posted by santhip
    Hi,

    I am new to DB2. Following is the trigger code I tried to execute in z/OS mainframe system.

    CREATE OR REPLACE TRIGGER TUBR01_POC1
    BEFORE INSERT OR UPDATE ON TABLE POC1
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    N.POC_COL2 = CURRENT TIMESTAMP;
    ---------+---------+---------+---------+---------+---------+---------+--------

    I am getting the following error.

    DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD TRIGGER, TOKEN FOR WAS
    EXPECTED
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 19 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000013' X'00000000' SQL DIAGNOSTIC INFORMATION

    Please throw some light on this error. I really appreciate your help

    Thanks
    Santhi

  3. #3
    Join Date
    Feb 2004
    Posts
    24

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hi Santhi,

    marc again.

    a little example:

    create trigger (triggername)
    no cascade before (activation time)
    update (trigger event)
    of (columnname) on (tablename)
    referencing old as old
    new as new
    for each row (granularity)
    mode db2sql (shit needed by db2)
    when (trigger condition) condition
    begin atomic (trigger action ends with ';')
    end@ (you must alter the sql terminator to a different sign than ';'
    for example '@')

    This is only an example !

    regards

    marc



    Originally posted by santhip
    Hi,

    I am new to DB2. Following is the trigger code I tried to execute in z/OS mainframe system.

    CREATE OR REPLACE TRIGGER TUBR01_POC1
    BEFORE INSERT OR UPDATE ON TABLE POC1
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    N.POC_COL2 = CURRENT TIMESTAMP;
    ---------+---------+---------+---------+---------+---------+---------+--------

    I am getting the following error.

    DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD TRIGGER, TOKEN FOR WAS
    EXPECTED
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 19 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000013' X'00000000' SQL DIAGNOSTIC INFORMATION

    Please throw some light on this error. I really appreciate your help

    Thanks
    Santhi

  4. #4
    Join Date
    Nov 2003
    Posts
    30

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hi,

    Thanks for your immediate reply.

    I modified the trigger as below.

    CREATE TRIGGER TUB_POC1
    NO CASCADE
    BEFORE INSERT
    ON TABLE POC1
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    N.POC_COL2 = CURRENT TIMESTAMP;


    I am getting following error now.

    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "REFERENCING". SOME SYMBOLS
    THAT MIGHT BE LEGAL ARE: OLD, NEW, OLD_TABLE, NEW_TABLE
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 289 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

    What we are trying to do is update a timestamp field before inserting any new row. Can we update the same table data on update trigger?
    Thanks
    santhi
    Originally posted by disaster
    Hi Santhi,

    marc again.

    a little example:

    create trigger (triggername)
    no cascade before (activation time)
    update (trigger event)
    of (columnname) on (tablename)
    referencing old as old
    new as new
    for each row (granularity)
    mode db2sql (shit needed by db2)
    when (trigger condition) condition
    begin atomic (trigger action ends with ';')
    end@ (you must alter the sql terminator to a different sign than ';'
    for example '@')

    This is only an example !

    regards

    marc

  5. #5
    Join Date
    Feb 2004
    Posts
    24

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hi,

    CREATE TRIGGER TUB_POC1
    NO CASCADE
    BEFORE INSERT
    ON POC1
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    SET N.POC_COL2 = CURRENT TIMESTAMP
    WHERE (enter search condition for the explicit row to be updated);
    END@

    It's import to alter the sql terminator to the character used after the
    keyword end. If u don't the statement ends after the ';' but this is not the end of the create trigger statement !!!

    In this example u are updating the same table the trigger is defined on.
    If u wanna update another table u have to write a update statement for that within the begin atomic/end step.

    regards

    marc

    Originally posted by santhip
    Hi,

    Thanks for your immediate reply.

    I modified the trigger as below.

    CREATE TRIGGER TUB_POC1
    NO CASCADE
    BEFORE INSERT
    ON TABLE POC1
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    N.POC_COL2 = CURRENT TIMESTAMP;


    I am getting following error now.

    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "REFERENCING". SOME SYMBOLS
    THAT MIGHT BE LEGAL ARE: OLD, NEW, OLD_TABLE, NEW_TABLE
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 0 -1 289 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

    What we are trying to do is update a timestamp field before inserting any new row. Can we update the same table data on update trigger?
    Thanks
    santhi

  6. #6
    Join Date
    Nov 2003
    Posts
    30

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hi Mark.

    Do I have to give explicit where condition to update the current record. I guess N represents the current row that is going to be created in this case. so when I say N.column it refers to single row. So I guess where condition is not required in my scenario. Please confirm this.

    I really appreciate your help. The problem is I dont have access to execute this trigger, I have to give it to DBA everytime I change for execution.

    Thanks alot once again
    Santhi

  7. #7
    Join Date
    Feb 2004
    Posts
    24

    Re: DB2 7.1 for z/OS - Create Trigger problems

    Hi,

    i think u are rught, my fault !!!

    regards

    marc

    Originally posted by santhip
    Hi Mark.

    Do I have to give explicit where condition to update the current record. I guess N represents the current row that is going to be created in this case. so when I say N.column it refers to single row. So I guess where condition is not required in my scenario. Please confirm this.

    I really appreciate your help. The problem is I dont have access to execute this trigger, I have to give it to DBA everytime I change for execution.

    Thanks alot once again
    Santhi

Posting Permissions

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