SEO

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?

"Mary" <db2trig@yahoo.com> wrote in message
news:cbf0c423.0312071653.1c517743@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)?

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:<M0QAb.465$eg3.38307@news.uswest.net>...
> "Mary" <db2trig@yahoo.com> wrote in message
> news:cbf0c423.0312071653.1c517743@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)?

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

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

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 <srielau@ca.eye-bee-m.com> wrote in message news:<br3evs$f2e$1@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

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