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
------------------------------

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
------------------------------

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:
Sent: Wed September 11, 2024 06:29 AM
From: Yves-Antoine Emmanuelli
Subject: Creating Trigger AFTER INSERT (DB2 V11.5)

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
------------------------------

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
------------------------------

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

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