By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,865 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,865 IT Pros & Developers. It's quick & easy.

DB2 multiple trigger activation order for column UPDATEs

P: n/a
We are developing a DB2 V7 z/OS application which uses a "trigger"
table containing numerous triggers - each of which is activated by an
UPDATE to a different column of this "trigger" table. When the
triggers are fired, various other operations are performed on other
tables in the database. The triggers are not created on these other
tables because other programs perform updates to these tables and we
do not want the triggers to fire for them. There is no trigger
cascading.

The problem appears to be that, even though different columns control
the trigger activation, any UPDATE to the "trigger" table causes
trigger execution to proceed in the order of trigger creation. For
example:

CREATE TRIGGER Trigger 1
AFTER UPDATE OF AA
ON TFP_TRIGGERS

CREATE TRIGGER Trigger 2
AFTER UPDATE OF BB
ON TFP_TRIGGERS
Although AA and BB are updated independently, Trigger 2 (newer
timestamp) waits until Trigger 1 has completed. So even though the
triggers seem to have different triggering events, DB2 appears to be
treating both triggers as having the same triggering event. Is there
any way to have trigger activation controlled at the column level so
that both triggers can be activated independently and run
simultaneously?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Mary" <db*****@yahoo.com> wrote in message
news:cb**************************@posting.google.c om...
We are developing a DB2 V7 z/OS application which uses a "trigger"
table containing numerous triggers - each of which is activated by an
UPDATE to a different column of this "trigger" table. When the
triggers are fired, various other operations are performed on other
tables in the database. The triggers are not created on these other
tables because other programs perform updates to these tables and we
do not want the triggers to fire for them. There is no trigger
cascading.

The problem appears to be that, even though different columns control
the trigger activation, any UPDATE to the "trigger" table causes
trigger execution to proceed in the order of trigger creation. For
example:

CREATE TRIGGER Trigger 1
AFTER UPDATE OF AA
ON TFP_TRIGGERS

CREATE TRIGGER Trigger 2
AFTER UPDATE OF BB
ON TFP_TRIGGERS
Although AA and BB are updated independently, Trigger 2 (newer
timestamp) waits until Trigger 1 has completed. So even though the
triggers seem to have different triggering events, DB2 appears to be
treating both triggers as having the same triggering event. Is there
any way to have trigger activation controlled at the column level so
that both triggers can be activated independently and run
simultaneously?


Are both updates (to AA and BB) done within the same unit of work (i.e., no
commit is done until both updates take place)?
Nov 12 '05 #2

P: n/a
Yes - they are in the same unit of work. This is a requirement of the application.

"Mark A" <ma@switchboard.net> wrote in message news:<M0*****************@news.uswest.net>...
"Mary" <db*****@yahoo.com> wrote in message
news:cb**************************@posting.google.c om...
We are developing a DB2 V7 z/OS application which uses a "trigger"
table containing numerous triggers - each of which is activated by an
UPDATE to a different column of this "trigger" table. When the
triggers are fired, various other operations are performed on other
tables in the database. The triggers are not created on these other
tables because other programs perform updates to these tables and we
do not want the triggers to fire for them. There is no trigger
cascading.

The problem appears to be that, even though different columns control
the trigger activation, any UPDATE to the "trigger" table causes
trigger execution to proceed in the order of trigger creation. For
example:

CREATE TRIGGER Trigger 1
AFTER UPDATE OF AA
ON TFP_TRIGGERS

CREATE TRIGGER Trigger 2
AFTER UPDATE OF BB
ON TFP_TRIGGERS
Although AA and BB are updated independently, Trigger 2 (newer
timestamp) waits until Trigger 1 has completed. So even though the
triggers seem to have different triggering events, DB2 appears to be
treating both triggers as having the same triggering event. Is there
any way to have trigger activation controlled at the column level so
that both triggers can be activated independently and run
simultaneously?


Are both updates (to AA and BB) done within the same unit of work (i.e., no
commit is done until both updates take place)?

Nov 12 '05 #3

P: n/a
AK
Mary,

TRIGGERS IN db2 ARE INLINE, I.E. THEY ARE COMPILED into the UPDATE
statement that fires the triggers. Just have a look at the execution
plan and see for yourself.

I guess you are thinking of parallellism because something runs
slowly. I'd rather post the performance problem
Nov 12 '05 #4

P: n/a
Actually triggers in DB2 z/OS are stored procedures.
It is only DB2 for Multiplatforms that inlines triggers.
Either way the SQL Standard requires Trigger A to finish before Trigger
B starts. Otherwise you could get funky semantics.
In theory a DBMS could analyse the triggers, detect there are no
conflicts and then parallelize execution.
I'm not aware of a product that does that.

Cheers
Serge

Nov 12 '05 #5

P: n/a
Thank you. Just for clarification - Does this mean that all trigger
execution is serialized when different programs running in parallel
activate multiple column triggers defined on the same table
simultaneously?

If so, would it solve our problem to move the triggers out of the
"trigger" table and create them separately on each of the tables
needing processing? We would need to code the WHEN clause to limit
the triggered action to a specific user so that the trigger bodies
would not execute for other applications. Any other ideas or
suggestions would be welcome.

Serge Rielau <sr*****@ca.eye-bee-m.com> wrote in message news:<br**********@hanover.torolab.ibm.com>...
Actually triggers in DB2 z/OS are stored procedures.
It is only DB2 for Multiplatforms that inlines triggers.
Either way the SQL Standard requires Trigger A to finish before Trigger
B starts. Otherwise you could get funky semantics.
In theory a DBMS could analyse the triggers, detect there are no
conflicts and then parallelize execution.
I'm not aware of a product that does that.

Cheers
Serge

Nov 12 '05 #6

P: n/a
Hi Mary,

What I wrote only referred to a single connection.
If you run, say, an update statement which fires triggerA and triggerB
Then the order is: UPDATE <constraintscheck> <triggerA> <triggerB>
Concurrent connections are handled as usual and appropriately to you
isolation level.

I can't comment on you "trigger-table" since I don't knwo DB2 z/OS
catalog structure.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.