| Author |
Message
|
| lancelotlinc |
 Posted: Mon Sep 23, 2013 7:04 am Post subject: Automatic Transaction Exists |
 |
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
WMB 8.0.0.2
Toolkit 8.0.0.2 iFix 001
MainFlow -> SubFlow
Inside the subflow:
Input -> Compute1 -> HttpRequest -> [Out]Compute2/[Error]Compute3
We've set the Transaction property on all three Computes to COMMIT. We have to make several HttpRequest calls , and to do this, we call PROPAGATE from Compute1. On the first invocation all the way through, everything works great and the Transaction property of all the Compute nodes are honored (COMMIT). On subsequent invocation, the dreaded ODBC DSN database error 'Transaction will be committed, but a pending automatic transaction exists' error occurs. We've opened a PMR but I thought I would share our experience.
Also the stack traces in the ExceptionList ignore the SubFlow title when it generates the :Label tag in the ExceptionList, so its like its complaining about a Compute node in the main flow when in reality the offending Compute node is in the subflow.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
 |
| nukalas2010 |
| Posted: Tue Sep 24, 2013 2:45 am Post subject: |
|
|
Master
Joined: 04 Oct 2010
Posts: 220
Location: Somewhere in the World....
|
What is the property for the MQInput Node - > Advanced - > Transaction mode??
Did you tried by setting the Transaction mode as "No"?? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 24, 2013 3:11 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| nukalas2010 wrote: |
What is the property for the MQInput Node - > Advanced - > Transaction mode??
Did you tried by setting the Transaction mode as "No"?? |
Yes. The flow behaves as expected with the first pulse. After that, the flow mis-behaves because the Transaction property on the Compute nodes are not honored.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 24, 2013 10:41 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
The net net of the PMR is, if a PROPAGATE is used, the ODBC cursor is still open, even if you explicitly call COMMIT. (Calling COMMIT has no effect in this instance.)
Therefore, you must not use PROPAGATE and let the logic hit the RETURN TRUE statement. To do this, you must put your iteration logic in an additional COMPUTE node that iteratively calls downstream nodes using PROPAGATE. This new COMPUTE node cannot have any database statements in it.
Alternately, we opted for the multiple ODBC connections route. This required us to create nine duplicate DSN identifiers.
P. S. If COMMIT is not effective, then why is it there? What's the purpose of COMMIT if you cannot use it?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Sep 24, 2013 2:39 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
So the behaviour you see is documented here:
http://pic.dhe.ibm.com/infocenter/wmbhelp/v8r0m0/topic/com.ibm.etools.mft.doc/ac17030_.htm
Essentially, the Commit option on the node only commits when all of the ESQL in the code has finished - that is when the RETURN TRUE/FALSE statement has been run at the end of the node. Therefore, when you call PROPAGATE in the "middle" of the ESQL, existing database work has not finished so the subsequent node throws an exception... I can see what you are trying to do and it does make sense, so an RFE here would help...
Kind regards,
_________________
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 |
|
|
| rekarm01 |
| Posted: Tue Sep 24, 2013 8:03 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
There are also "uncoordinated auxiliary transactions". The message flow can commit them independently of the node boundaries.
| lancelotlinc wrote: |
| P. S. If COMMIT is not effective, then why is it there? What's the purpose of COMMIT if you cannot use it? |
COMMIT only works for UNCOORDINATED transactions. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Sep 25, 2013 3:38 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 26, 2013 10:42 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| lancelotlinc wrote: |
| mgk wrote: |
| an RFE here would help... |
RFE ID: 39605 Optimized database connection management. |
Posting the RFE as a link would help other people to vote on it!
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Fri Sep 27, 2013 3:25 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
|
|
