Db2

 View Only

  • 1.  Creating Trigger AFTER INSERT (DB2 V11.5)

    Posted Tue September 10, 2024 04:24 PM 
    Hello everyone,

the idea I'm trying to realize is to create a trigger immediately after the data was insert into a table. Trigger should log info about who inserted data and when. I will provide code below.
    --<ScriptOptions statementTerminator="@"/>
CREATE OR REPLACE TRIGGER DEV_SCHEMA.TEST_TABLE
	AFTER INSERT ON DEV_SCHEMA.TEST_TABLE
	FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO DEV_SCHEMA.LOG(OPERATION_TYPE,TABLE_NAME,OPERATION_TIME, USERNAME) 
VALUES('INSERT','DEV_SCHEMA.TEST_TABLE',CURRENT_TIMESTAMP,SESSION_USER);
END @

    TEST_TABLE is row based organized table. Problem occurs when I want update records in  TEST_TABLE using Data Stage. What happens is database lock.



    ------------------------------
    Mirza Tinjak
    ------------------------------


  • 2.  RE: Creating Trigger AFTER INSERT (DB2 V11.5)

    Posted Wed September 11, 2024 06:30 AM
    Edited by Yves-Antoine Emmanuelli Wed September 11, 2024 06:31 AM

    Hello Mirza,

    It is not recommended to do DML operations directly through SQL concurrently with DML generated by an ETL tool such as Datastage. Would it be possible to perform the same type of logging through Datastage ?

    Alternatively, if you absolutely need to implement this logging outside of Datastage, can you tell us which SQL statements are generated when the update of TEST_TABLE fails?

    You could also create an event monitor for locking in order to capture the statements involved in the lock.

    Hope this helps.

    Regards.



    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------

    Original Message


  • 3.  RE: Creating Trigger AFTER INSERT (DB2 V11.5)

    Posted Wed September 11, 2024 06:45 AM

    Hello Yves-Antoine Emmanuelli,

    thank You for reponse. 

    "It is not recommended to do DML operations directly through SQL concurrently with DML generated by an ETL tool such as Datastage. Would it be possible to perform the same type of logging through Datastage ?"
    I hope there is a way to implement logging using Datastage, but I am not aware of it. The first solution that comes in mind is after SQL statement for logging in target DB2 connector. However, this solution seems exhaustive because it has to be adjusted for each DS job.

    "Alternatively, if you absolutely need to implement this logging outside of Datastage, can you tell us which SQL statements are generated when the update of TEST_TABLE fails?"
    The error is as follows:

    DB2 reported: SQLSTATE = 40506: Native Error Code = -1476: Msg = [IBM][CLI Driver][DB2/LINUXPPC64LE] SQL1476N  The current transaction was rolled back because of error "-911".  SQLSTATE=40506


    Best regards,

    Mirza



    ------------------------------
    Mirza Tinjak
    ------------------------------

    Original Message


  • 4.  RE: Creating Trigger AFTER INSERT (DB2 V11.5)

    Posted Thu September 12, 2024 10:00 AM

    Hi Mirza,

    Your first statement "...create a trigger immediately after the data was insert into a table...."....., are you creating the trigger after every insert, or is the trigger created and just firing after each insert?  You should create the trigger once and it should just fire with each insert.

    Your trigger does not contain compound statements so you don't need the keywords BEGIN/END.

    I am by no means an expert on triggers, but I would rewrite the trigger as below: 

    --<ScriptOptions statementTerminator="@"/>
    CREATE OR REPLACE TRIGGER DEV_SCHEMA.TEST_TABLE_INSERT
      AFTER INSERT
      ON DEV_SCHEMA.TEST_TABLE
      FOR EACH STATEMENT
    INSERT INTO DEV_SCHEMA.LOG (OPERATION_TYPE,TABLE_NAME,OPERATION_TIME, USERNAME) 
    VALUES('INSERT','DEV_SCHEMA.TEST_TABLE',CURRENT_TIMESTAMP,SESSION_USER)
    @

    Keep in mind, by using the 'FOR EACH STATEMENT' option I believe you're going to insert a row into DEV_SCHEMA.LOG whether your insert statement is successful or not.

    Regards



    ------------------------------
    Andy McLaughlin
    Great American Insurance
    Cincinnati
    ------------------------------

    Original Message


  • 5.  RE: Creating Trigger AFTER INSERT (DB2 V11.5)

    Posted Thu September 12, 2024 11:07 AM

    Could you use the DB2AUdit Facility to get the information you are after?  Auditing is designed specifically for this type of process.



    ------------------------------
    Edward Spencer
    ------------------------------

    Original Message