will it drain when create trigger?

dejunzhu · Active User Joined: 08 May 2008 Posts: 390 Location: China

I wonder if DB2 will drain out when trigger is created on the tables that are being used by online transactions? I cannot find out the answer from DB2 manuals.

on the other side, will DB2 drain when trigger is dropped?

dbzTHEdinosauer · Posted: Thu Sep 20, 2012 6:46 pm

you drain a resource.
a trigger is not a resource, it is a set of actions.
once a trigger is CREATed successfully, the actions will be available.
once a trigger is DROPped successfully, the actions are no-longer available.

to answer what I think is your implied question:
if there are 20 UPDATE transactions queued against a table,
and the CREATE Trigger is successful before the 10th transaction starts,
then the actions of the trigger will be available for the 10th.
the first 9 do not have the benefit of the Trigger.

If the above is true, the converse (DROP) would be true.

dejunzhu · Active User Joined: 08 May 2008 Posts: 390 Location: China

I did a test as below:
first, in my test program, I issue update statement against a table, and then I suspend the execution for 30 seconds before commit by issue 'EXEC CICS DELAY' statement in order to sustain the lock of the table.

then , I run the program.

after the program starts running, I did below test SEPARATELY:
1. I issue 'CREATE TRIGGER' statement against the table that the test program is updating. The result shows 'CREATE TRIGGER' statement was executed successfully as soon as it is issued.
2. I issue 'DROP TRIGGER' statement (the trigger to be dropped is created in test case 1) when the test program starts running. Results shows the 'DROP TRIGGER' SQL statement was not executed until the test program ends.

so, I got below conclusion:
'CREATE TRIGGER' DOES NOT need to drain, while 'DROP TRIGGER' needs to drain.

dbzTHEdinosauer · Posted: Thu Sep 20, 2012 8:53 pm

fine,
but,
the program running with the lock and delay did not employ the trigger during the Create.
but the program running with the lock and delay were employing the trigger, so the trigger could not be removed until the program finished (a commit).

but you have isolated the situation with only one program running.

what about multilple programs and then map out the effect

my suppositions still stand:
once the trigger is created, then it takes effect.
once the trigger is deleted, then it stops taking effect.

you are not using the term drain properly.
a trigger is not a resource. the table is a resource.
you are not draining anything, if you are waiting for a 'lock' to be released.