| Author |
Message
|
| KrotaZ |
 Posted: Fri Jun 03, 2011 4:47 pm Post subject: TRANSACTIONALITY ON MULTIPLE DB MANAGERS AND COMPUTE NODES |
 |
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
Hi.
I've a doubt and I hope someone can help me. I'm facing the next scenario:
| Code: |
| INPUT NODE ---> COMPUTE NODE 1 -----> COMPUTE NODE 2 -----> OUTPUT |
On COMPUTE NODE 1 Data source property I've configured a Db2 data source and on COMPUTE NODE 2 Data Source Property an SQL Server Data source.
What I need is ensure that if something fails at the moment of calling the INSERT function on the sQL server node the data flow
rolls back all changes made during the flow, wich means to roll back the inserts made by the COMPUTE NODE 1. Is this possible?? Do you guys
have an example of how to do this or can you tell me how to look for this??
I know this is possible using java compute nodes but as there will be many instances of the flow I'd like to delegate the database connection management
to the ODBC instead of having connection. |
|
| Back to top |
|
 |
| jlaisbett |
| Posted: Fri Jun 03, 2011 5:21 pm Post subject: |
|
|
Apprentice
Joined: 27 Nov 2009
Posts: 39
|
Theoretically it will work under the following scenario:
- Make sure the input node supports transactions and they are enabled
- Set Transaction to Automatic on both compute nodes
- Make sure you can detect when an insert fails in compute node 2
- When the insert fails throw an exception
- Make sure the exception makes it back to the input node and the catch terminal is not connected
That should cause a rollback of both.
I have never tested this with DB2 and SQL Server in the same flow but I've never come across anything to suggest that it wouldn't work for multiple database types. |
|
| Back to top |
|
|
| KrotaZ |
| Posted: Wed Jun 08, 2011 8:54 pm Post subject: |
|
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
@jlaisbett: Thanks for your reply I tried what you told me to and the result was the expected one, the problem is that now I've the next scenerio
| Code: |
INPUT NODE ---> TRYCATCH NODE -
TRY: COMPUTE NODE 1 -----> COMPUTE NODE 2 -----> OUTPUT
CATCH: COMPUTE NODE 3----> OUTPUT |
I need to catch the exception generated on any of the COMPUTE NODES of the TRY line generate an output message and rollback any thing that has happened on the COMPUTE NODE 1 AND COMPUTE NODE 2 where COMPUTE NODE 1 dynamically inserts in multiple DB2 DATABASES and COMPUTE NODE 2 inserts in multiple SQL SERVER DATABASES. I've read many pages and posts and I belive this is almost impossible to do, but I'd like to check with you and all the other guys if my idea is correct.
Thanks alot. |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Wed Jun 08, 2011 10:41 pm Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
Using PASSTHRU statement giving ROLLBACK as statement could help. But you will have to send it to each DSN used before.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jun 09, 2011 12:55 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If COMPUTE NODE 3 was set to "Commit" then it would commit its work on the nodes DSN when it has finished. Also the same for the MQOutput node. Then wire a THROW node after the output node. This will cause a rollback for COMPUTE NODE 1 and 2 if the exception gets back to the input node.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| KrotaZ |
| Posted: Thu Jun 09, 2011 5:35 am Post subject: |
|
|
Novice
Joined: 30 Mar 2011
Posts: 11
|
| j.f.sorge wrote: |
Using PASSTHRU statement giving ROLLBACK as statement could help. But you will have to send it to each DSN used before. |
I'll try this, I'll inform the results. Thanks!!!
| mgk wrote: |
| If COMPUTE NODE 3 was set to "Commit" then it would commit its work on the nodes DSN when it has finished. Also the same for the MQOutput node. Then wire a THROW node after the output node. This will cause a rollback for COMPUTE NODE 1 and 2 if the exception gets back to the input node. |
Thanks for your quick answer guys. I've some questions.
1.- What does DSN stands for? Dynamic Source Name?
2.- Having the scenario I quoted
| Code: |
INPUT NODE ---> TRYCATCH NODE -
TRY: COMPUTE NODE 1 -----> COMPUTE NODE 2 -----> OUTPUT
CATCH: COMPUTE NODE 3----> OUTPUT |
COMPUTE NODE 3 doesn't access database it only generates the output message in case of an exception so does it have to be in "commit" mode?
3.- I understand I've to propagate the exception to the Input Node, but the TRY CATCH node after the Input Node won't let it pass even with a Throw node, is there any other way of "jumping" the Try Catch Node??
Thanks |
|
| Back to top |
|
|
|
|
