| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to rollback multiple table inserts in a Compute node |
« View previous topic :: View next topic » |
| Author |
Message
|
| prak |
 Posted: Mon Sep 02, 2002 9:16 pm Post subject: How to rollback multiple table inserts in a Compute node |
 |
|
Newbie
Joined: 26 Apr 2002
Posts: 9
|
I have a Compute node wired to the Try terminal of a Try-Catch Node.
In the Compute Node, I am using "passthru" to insert data into multiple DB2 tables. If an insert in a particular table fails, I want to generate a User Exception (or a Database Exception is fine as well), rollback all the previous inserts and send the message back to the catch terminal of the Try-Catch Node.
Everything works except the rollback. I tried using passthru('rollback') in an "Error Handler Compute Node" wired to the catch terminal but that didn't work. The data remained in the tables that were successfully inserted into before the error.
How have you handled such a situation where we need to make all database updates into one single UOW in a Compute Node?
Thanks in advance.. |
|
| Back to top |
|
 |
| prak |
| Posted: Mon Sep 02, 2002 9:22 pm Post subject: |
|
|
Newbie
Joined: 26 Apr 2002
Posts: 9
|
Addendum to the above:
The Compute Node in which we are doing the datasbae updates appears after MANY other nodes doing other things in the message flow.
So, I don't want the WHOLE mesage flow to be rolled back - only the stuff that happened in this one Compute Node that is doing the database updates.
Thanx. |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Sep 04, 2002 12:50 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Prak,
If you want DB transactions to commit/rollback with MQ Commit/Rollback, you should use 2-phase commit protocol by running your message flow in Globally Coordinated Transaction mode. Not all database versions are supported for 2-phase commit with MQ.
In case of failure, DB transactions will rollback only when the Exception is caught at MQInput node. If you have MQInput node's catch terminal connected, your catch path should end with a Throw node.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| prak |
| Posted: Wed Sep 04, 2002 4:03 pm Post subject: |
|
|
Newbie
Joined: 26 Apr 2002
Posts: 9
|
Kiran,
The DB version I am using is DB2 7.1.
However, doesn't your sugestion imply a complete Message Flow Rollback?
As I mentioned in my addendum, I only want to rollback database transactions that happened in ONE single Compute node - a compute node that is towards the end in a chain of many nodes.
A friend has suggested doing a passthru('rollback') when I catch a SQL Exception. He says that doing so will rollback everything that happened in that particular compute Node upto the time the exception happened. Didnt seem to work for me. Does that work for anybody else?
Thanks. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Sep 05, 2002 2:13 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Prak,
Yes, my suggestion imply complete Message Flow Rollback. I don't think passthru('rollback') will work here. Could you please explain your message flow in detail?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| prak |
| Posted: Fri Sep 06, 2002 5:24 pm Post subject: |
|
|
Newbie
Joined: 26 Apr 2002
Posts: 9
|
Here is the message flow (simplified):
MQInput 1-->Compute Node C1-->Custom Node CN2--> Custom Node CN3-->TryCatch 1 [Try]-->Compute Node C4 [out] --> MQOutput 1
TryCatch 1 [Catch]-->Compute Node C5 [out]--> MQOutput 1
C4 is a compute node that appears after a bunch of nodes as shown above.
In C4, I insert into three tables T1, T2 and T3 using passthru.
If any table insert gives an SQL exception, I catch it, throw a User Exception that is then sent back to Try-Catch 1. From there it goes to Compute Node C5 which I handle the exception and do other work.
The problem is that I want to roll back the insert in table T1 if T2 or T3 fails but I do not want to rollback the work done in previous nodes such C1, CN2 and CN3.
How can I accomplish this?
Thanks.. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Sep 09, 2002 7:37 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Prak,
By default Message Flow runs in Broker Coordinated mode. In this mode, if you are doing any DB updates, broker will make sure these transactions are committed if the MQ transaction is successful. It does not guarantee that the db transactions will be rollback if any failure occurs.
To make sure your db transactions are rolled-back, you should run your message flow in Global coordinated transaction mode by configuring queue manager for 2-phase commit. In this mode, DB trans will rollback only when the error is thrown back to MQInput node.
In your case, you have a try-catch node in the out path. If any db update fails in C4 node, message will rollback and it will propagate to C5 node. If using 2-phase commit, you should throw back the error using Throw node in the catch path. If your processing can be separated for C4/C5 node, try using two different message flows, in first message flow you will have nodes C1, CN2, and CN3 and the second message flow will contain C4 and C5 nodes.
If you don’t want to use 2-phase commit, you will have to handle the db trans manually, for example, In C4 node, if you are inserting data into 3 different tables, if any error occur, you should delete these records in C5 node.
Hope this helps.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Segs |
| Posted: Mon Sep 09, 2002 10:56 am Post subject: |
|
|
Voyager
Joined: 04 Oct 2001
Posts: 78
Location: Zurich Financial Services
|
| I've managed to get all this working on the platforms you're using, are you still having problems of have you now got it working OK? |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
