MVSFORUMS.com

Bithead · Posted: Wed Oct 22, 2003 1:04 pm Post subject: DB2 Triggers

I need to propagate some DB2 table changes made by a vendor application to a number of other applications so I have set up some triggers and stored procedures as follows:

1. Trigger_1 will execute when the vendor application changes a field on table_A (Trigger_1 is defined as AFTER UPDATE OF);

2. Trigger_1 invokes StoredProcedure_1 which will update a value on table_B (StoredProcedure_1 is defined as COMMIT NO);

3. Trigger_2 will execute when the table_B is updated (Trigger_2 is defined as AFTER UPDATE OF);

4. Trigger_2 invokes StoredProcedure_2 which will update a value on table_C (StoredProcedure_2 is defined as COMMIT NO);

If I activate only Trigger_1, I can see any changes made to table_A appearing on table_B;

If I activate only Trigger_2, I can manually update table_B and see the changes on table_C;

If I activate both triggers, then the vendor application hangs until I cancel the DB2 thread.

What is going on here?

kolusu · Posted: Wed Oct 22, 2003 1:45 pm Post subject:

Bithead,
I guess the stored procedure definitions for commit are not defined properly.I mean if you activate TRIGGER_1 it should update only TABLE_A since the stored procedure is defined as COMMIT NO.But you are seeing the updates of Table_B.

Did you define it as

Bithead · Posted: Wed Oct 22, 2003 1:50 pm Post subject:

Kolusu,

If I code COMMIT ON RETURN YES, then I am committing the changes before the vendor application does. If it has to rollback, it can't.

How do I code it to get it to cascade properly without issuing the commit?

kolusu · Posted: Wed Oct 22, 2003 2:48 pm Post subject:

Bithead,

How about invoking SP_2 from SP_1 after the update of table_B?(Remove trigger_2).In this way the vendor changes are committed if everything goes well. But you will miss table_C updates for the manual update of table_B.

Here is something I found in the application programming manuals about TRIGGER CASCADING

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/2.6.5?DT=20010710165542

You may also want to take a look at the link for Programming for the Instrumentation Facility Interface (IFI)

Kolusu

Bithead · Posted: Wed Oct 22, 2003 3:01 pm Post subject:

Kolusu,

I have a copy of the documentation but thanks anyway. I omitted to say that I have other processes that update table_B and these need to be propagated to table_C (but not table_A). Hence the need for two triggers.

The IFI looks like it needs a task running all the time or can be run in batch. I already have a batch solution but it is not real-time. I would like to avoid the task if at all possible.

I will have to look at combining the stored procedures into 1 so that a change to table_A will propagate to table_B and table_C. I will have to change the batch job that also changes table_B to also populate table_C.

It is not what I was hoping for but I can say that about alot of things..

Thanks for your help.

kolusu · Posted: Wed Oct 22, 2003 3:40 pm Post subject:

Bithead,

Can you add a column to Table_b? It will be just a 1 byte flag column.change the trigger_2 to kick off when updating the new column.

Vendor updates TABLE_A and this will kick off SP_1 which updates TABLE_B and within SP_1 itself invoke SP_2 which will update TABLE_C.So if everything goes well the changes are committed.

Now the manual update process will update the usual column of TABLE_B and also the new column with some value. Now trigger_2 will kick off as soon as the new column in TABLE_B is updated.The trigger_2 will now update the Table_C

Hope this helps...

cheers

kolusu

Bithead · Posted: Wed Oct 22, 2003 3:59 pm Post subject:

Sounds intruiging. I will try it.

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

kolusu · Posted: Thu Oct 23, 2003 5:03 am Post subject:

Manoj,

I think you overlooked this part of bithead's reply which was posted 3:01 Pm

manojagrawal · Beginner Joined: 25 Feb 2003 Posts: 124 Topics: 29

Hi Kolusu,

Yep! I got that now Very Happy

_________________
Thanks & Regards,
Manoj.

Bithead · Posted: Thu Oct 23, 2003 8:35 am Post subject:

Kolusu,

I have scheduled the batch process to run when the online application is down. I will still look at triggering off the 1 byte field and have duplicate code as my original scenario does not appear to work.