RE: Commit in Trigger process

At 08:06 09/14/2000 , Howard Weatherly wrote:

>Maybe I misstated, the commit is in the a program that the trigger calls, 
>the only function of the trigger is to initiate an update processes. 
>Basically here is what is happening, we run DpropR between the mainframe 
>and the AS400. The tables are updated on the mainframe and propagated to 
>the AS400. Since we can not propagate the other way because it would be 
>like a dog chasing its tail and never quite catching it we decided  to 
>update on the 400 side and use DRDA to update the mainframe. The 400 has 2 
>collections, one for the propagated tables and one for native tables, the 
>native tables are the drivers to update the mainframe which will be 
>reflected in the propagated tables on the 400. One table contains simple 
>rows that have a code and some dates in it, this row is add when a 400 
>process updates the native tables. We have an insert trigger on this 
>table. The trigger receives the code in the trigger buffer and decides 
>which process needs to be done on the mainframe and calls the necessary 
>program.
>
>Now in that context the program that inserts the row into the table that 
>causes the trigger has done some other updates on the 400, should that 
>program commit before inserting to the table that will cause the trigger 
>or should it issue a commit/rollback after that insert? or 
>should/would  the called program from the trigger be responsible for the 
>upstream commit.
>
>My opinion is that as soon as the program that initiated the trigger 
>ends,. the commit is going to be done by the system and if it did do a 
>rollback, it should be done only upon discovering that the insert to the 
>table that will cause the trigger fails, that way the trigger will not 
>occur, or will it?
>
>In any case yes the called DRDA program will be using two phase commit as 
>it also must update 400 tables but its primary mission is to update the 
>tables on the mainframe that will be/are propagated.

Howard,

I'm just rambling here, but my understanding is that if it's a before 
insert trigger, the insert could fail after the trigger is initiated. If 
it's an after insert trigger, the update has already occurred and can't 
fail, but can still be rolled back. The trigger is not required to run 
under the same commitment definition as the program that initiated it. (It 
could run with no commitment control.) Maybe that's what you really need to 
do. If you can get any feedback from the two phase commit, maybe you could 
raise an error in the initiating job that it could monitor and use to 
condition a rollback. I'm not sure it's even possible. If it is, I think it 
would need to be an after insert event, and commitment would need to wait 
for some feedback from DRDA. Possibly the trigger could wait for a response 
via a data queue and issue an exception if the remote job failed, although 
I'd be concerned about the speed if there's any volume involved.

See if this document helps.
http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/db2/rbafomst2 
90.htm


Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---