| Author |
Message
|
| deepak_paul |
 Posted: Sun Nov 20, 2011 2:20 pm Post subject: DB2 Insert in Exception Handler |
 |
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
All,
I have a subflow which has JCN to make JDBC type 4 connection to Database DB2. The same subflow is used in main message flow as well as in Exception hanlder subflow with different SQL inserts on the same Database.
I have a flow design as below.
Main message flow:
[MQ Input Node]-->[Some Processing nodes]-->[Subflow calls JCN]-->[MQOutput Node]
Exception handler subflow:
Catch/Failure terminal of MQ Input node-->[Some Processing Nodes]-->[Subflow calls JCN]
Now the issue is whenever i have problem in writing the final message to MQ out node in the main message flow,
1. I want the DB insert which was done through the subflow in main message flow to be completely rolled back.
2. And since the MQ write exception goes to Exception handler and is handled there, i want to in see only this record in the Database.
But what is happening is since the exception is handled in the exception handler, the whole message flow transaction gets complete and so it commits both inserts in Database. As a result, i see two records in Database.
The possible immediate solution could be i can set commit in the exception handler with having a condition to make sure the control is coming from Exceptional handler and introduce a throw node after that.
But i dont think that is recommended, since i see http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac30494_.htm that We shoudl not use COMMIT or ROLLBACK.
Please help me to figure out how i can see only one record that is coming through Exception handler.
_________________
Regards
Paul |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Mon Nov 21, 2011 10:28 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Hi Paul. One trick we use is to separate out our DB2 code into their own children message flows. We use an XSD to define the structure to pass from the parent flow to the children flow through MQ. Since we do not need feedback from the DB operations, we use MQ. In some cases, we use a Web Service if we want to take different logic path based on the DB result. Lance
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak_paul |
| Posted: Sat Nov 26, 2011 5:40 pm Post subject: |
|
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
| lancelotlinc wrote: |
| Hi Paul. One trick we use is to separate out our DB2 code into their own children message flows. We use an XSD to define the structure to pass from the parent flow to the children flow through MQ. Since we do not need feedback from the DB operations, we use MQ. In some cases, we use a Web Service if we want to take different logic path based on the DB result. Lance |
Hi lance,
Thanks for your reply and suggestion. Is there a way we can resolve this problem without making design change/introducing mq queue. If you have any other suggestions, please let me know. Thanks.
_________________
Regards
Paul |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Nov 28, 2011 5:54 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You could use a Web Service call in place of MQ. The point is, you need a separate transaction boundary that is not related to the first.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Nov 28, 2011 8:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| You could use a Web Service call in place of MQ. The point is, you need a separate transaction boundary that is not related to the first. |
I'm not sure that it would help in this case...
It seems like they are reusing the same transactional code in two different transactional contexts, and so I'm not sure that changing the nature of the transaction in effect will resolve anything.
Although, of course, a web service call is not transactional. Which still doesn't resolve the issue.
What might work is to promote the transactional property of the JCN to the subflow, such that it is explicitly set by the end user when they include it in their flow. This would allow for it to be set to COMMIT in the exception flow and use the same transaction in the main logic. |
|
| Back to top |
|
|
| deepak_paul |
| Posted: Tue Nov 29, 2011 6:31 pm Post subject: |
|
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
| mqjeff wrote: |
....
What might work is to promote the transactional property of the JCN to the subflow, such that it is explicitly set by the end user when they include it in their flow. This would allow for it to be set to COMMIT in the exception flow and use the same transaction in the main logic. |
There is NO property called transactional property in JCN, if you mean JCN for Java Compute Node.
All,
Will it be wise if we use normal JDBC type 4 java API in Java Compute Node where we can have own COMMIT and ROLLBACK on that atomic transaction of JCN rather than using broker supplied JDBC Java API method?
_________________
Regards
Paul |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Nov 30, 2011 5:45 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
COMMIT/ROLLBACK inside Compute nodes (JCN or ESLQ) is only good for the duration of that node. There is no automatic compensation if the downstream processing rollsback. Manual transactional compensation must be implemented in the error processing part of your flow.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak_paul |
| Posted: Sat Dec 03, 2011 9:23 am Post subject: |
|
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
| lancelotlinc wrote: |
| COMMIT/ROLLBACK inside Compute nodes (JCN or ESLQ) is only good for the duration of that node. There is no automatic compensation if the downstream processing rollsback. Manual transactional compensation must be implemented in the error processing part of your flow. |
Agreed. So the current approaches to solve this situation could be,
1. Seperate out the exception handler DB save to another message flow with interfacing WMQ queue and introduce a Throw node to make the message flow transaction NOT complete so that first save will not occur. [as throw node introduced, retry will happen. We can have custom mechanism to avoid retry also.]
2. Or use normal JDBC API in JCN to commit the first DB insert and roll back successfully if there is any failure/exception in downstream nodes.
Please let me know if we can add more and see which one is more reliable and efficient.
_________________
Regards
Paul |
|
| Back to top |
|
|
| inMo |
| Posted: Mon Dec 05, 2011 12:21 pm Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
IMHO... Your problem is in the design itself, so correcting it without a design change is tough. You have to take the error handler's external update out of the flow's unit of work (as previously suggested), but that is restricted by your design's re-use of the subflow that is in the main transaction. This sounds similar to the idea of painting yourself into a corner, but at least in that scenario the paint would dry at some point.
I can't help but ask about an error handler that is dependent on the same exact code that is in a main transaction? Is it not the point of an error handler to deal with unexpected problems caused by the main transaction's code base? |
|
| Back to top |
|
|
|
|
