| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Database commit in flow |
« View previous topic :: View next topic » |
| Author |
Message
|
| EvolutionQuest |
 Posted: Fri Sep 19, 2003 8:30 am Post subject: Database commit in flow |
 |
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
| When doing SQL stored procedures in a Compute Node should a throw force a rollback, at the documentation seems to leave one to believe or is it required to do a RETURN and use a Filter Node to force a rollback? |
|
| Back to top |
|
 |
| Craig B |
| Posted: Fri Sep 19, 2003 8:41 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
In a message flow, a transactional roll-back can only occur if processing returns to the MQInput node, and its Catch terminal is not wired or a has already been propagated to.
Therefore when you are discussing errors from compute nodes, then if you want a transactional rollback to occur, then you need to write your error handling logic such that the processing can return to the MQInput node.
When THROWing errors manually is discussed then this is for those for users who write error handling logic to examine what error has been thrown. If the error is such that they cannot handle it then they can manually throw an exception which usually will take them out of their error handling path. But once again the same applies. If you want a transactional rollback, then you have to make sure that this THROW results in processing flowing back to the MQInput node as mentioned in the first paragraph.
Hope this helps.
_________________
Regards
Craig |
|
| Back to top |
|
|
| EddieA |
| Posted: Fri Sep 19, 2003 9:37 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Craig,
How do you get a flow to roll back to the Failure terminal on the Input node manually. If you have a Throw, don't you have to have a Catch somewhere. I thought a Throw without a Catch was like not wiring an Output terminal. The data just vapourised.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Craig B |
| Posted: Fri Sep 19, 2003 10:05 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
Well Im not sure I fully understand your question as a whole, but in it you ask how to get an exception to go to the failure terminal of the Input node? Well from a user perspective it is not possible to perform operations such that you can get the message to go there during your failure processing. The failure terminal on the MQInput node is there to handle errors that are generated from within the MQInput node itself. This can either be due to a problem with retrieving a message from the queue, conversion errors, or simply that the MQMD.BackoutCount exceeded the threshold of the input queue. So basically the failure terminal is only propagated to if the Input nodes deems that the message cannot be propagated out of the Out terminal.
This is the same for any Primitive node. The failure terminal is designed to handle exceptions that are generated from within the node itself. So if your processing generates an exception, then the failure terminal is propagated to if wired.
If not wired the processing will then flow back, to the first point that can handle the error. This can either be a TryCatch node, or a node with a catch terminal on it. So the difference now comes in that failure terminals handle errors from within a node, catch terminals handle errors that have come from downstream in the flow.
If an error occurs in an error handling path, such as on a path from a failure terminal, or a catch path then the same logic applies in that the flow will flow back to the next available error handler that can process the error from that point.
In the information above, we have made reference to when an error occurs. This can either be as a result of an exception generated by the flow processing, or by encountering a Throw node, or ESQL THROW statement. These manual ways of throwing an error, cut an exception list record and then invoke the same error handling as described above.
When talking about error handling it is helpful to distinguish between the terminology of flow back and roll back. Where flow back means the processing returns to an earlier point in the flow, and roll back means a transactional roll back occurs. In a flow there are many ways processing can flow back to a previous point, but as stated in the previous append, there is only one user situation which can lead to a transactional rollback.
This maybe a longer explanation than you needed, but hopefully it covers your question. And its Friday afternoon and Im bored. 
_________________
Regards
Craig |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Sep 19, 2003 11:23 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
So, if I understand this correctly even though I have the failure terminal wired on the compute node and throw a user execption within it, it will not do a transactional rollback? I must instill a Try/Catch node in order to get a transactional rollback or not wire the failure terminal and let the mqinput failure terminal resolve the transactional rollback? Also, from my understanding/reading (god help us all) if the catch terminal is not wired on the mqinput node then the exceptions will go to the failure terminal if it is wired, correct?
Thank you. |
|
| Back to top |
|
|
| EddieA |
| Posted: Fri Sep 19, 2003 11:57 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
As well as it being Friday, I'm sitting in an airport. 
That wasn't quite what I was asking. Lets see if I can put it clearer.
If I have a Throw node or I use the THROW statement, where does the message go if I don't have a Try/Catch node in the flow and I haven't wired the Catch terminal of the Input node.
The last time I tried this, as a quick test for something else, the message just disappeared, which led me to believe that I had to have some form of Catch in the flow. It DIDN'T go to the Failure terminal.
This was the reason for my question, as if I can't somehow 'force' the message to the Failure terminal, I can't get any DB rollback.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Craig B |
| Posted: Fri Sep 19, 2003 11:59 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
To answer your questions :
Q) So, if I understand this correctly even though I have the failure terminal wired on the compute node and throw a user execption within it, it will not do a transactional rollback?
A) When you issue a THROW, then it will attempt to propagate to the failure terminal if it is attached. So if you have wired the failure terminal, then the processing will continue down this path. By the virtue that you have wired the failure terminal, you are saying "I am handling the error because it might be something I can recover from". So there is no transactional roll back, and no errors generated in the local log. You are handling the error.
Q) I must instill a Try/Catch node in order to get a transactional rollback or not wire the failure terminal and let the mqinput failure terminal resolve the transactional rollback?
No. Inserting a TryCatch node will not invoke a transactional rollback. Once again, by using TryCatch nodes you are signalling "I will handle the error to see if I recover from it". The different between Failure terminals and TryCatch nodes is that a failure terminal is for you to handle an error in one specific node. A TryCatch block allows you handle error for all the nodes that were wired to the out terminal of the TryCatch node. Obviously if a failure terminal was wired on one of these nodes, then that takes precedence for that node.
Wiring the failure terminal of the MQinput node will not result in a transactional rollback. It is the NOT wiring of the catch terminal of the MQInput node that causes the transaction to rollback. Or if the catch terminal has already been propagated to, and we are on our way back out of it.
Q) Also, from my understanding/reading (god help us all) if the catch terminal is not wired on the mqinput node then the exceptions will go to the failure terminal if it is wired, correct?
No. If processing returns to the MQInput node, because you have not wired any error handling (or have THROWN out of your error handling), and you have not wired the catch terminal of the MQInput node, then that flow instance has ended. Therefore commit control kicks in and because you have had errors, the transaction has rolled back.
The Message goes back on the Input queue, the database updates are backed out. That flow "instance" no longer exists. The flow then looks to pick the next message up on the input queue, which just so happens to be the one that it just backed out. The MQMD.BackoutCount will be examined and if it is equal or greater than the threshold for the queue, then it is routed down the failure terminal of the MQInput node. Ie ... it never makes it to the output path. The Failure terminal of the Input node is only used when a failure occurs inside the MQInput node, such that it cannot propagate to the Out terminal. If in this case your input queue threshold was 10, then the message would go out of the out terminal and be tried again. If this was transitory failure such as DB connectivity issue, then you would want it to be tried again, because this time the processing may be successful.
This we only rollback if the processing returns to the Input node and the user did not handle the error is the only logical model to adopt. You can only decided to commit or rollback when the flow has finished processing all its paths. Ie All updates are done and everything was successful so commit OR an error occured, and the user has executed all paths so we rollback. So if you present the flow with more paths to go down, then the flow has not ended, and could do more updates within this unit of work, which could involve updating a DB or writing a failure message in Syncpoint. If the flow allowed you to rollback the transaction in the middle of the flow, then you could in theory could then go to a node that updates a database. Then what Unit of Work would this be in?? This would break the atomic nature of a message flow.
So the error handling model offers you the following :
1) Handle errors specifically in one node by wiring the failure terminal. User is stating they are handling the error and they might recover.
2) Use TryCatch nodes to handle errors for multiple nodes that are wired to the Try path. Offers single point of failure processing and user is stating they are handling the errors and they might recover.
3) Wire the MQInput node catch terminal to handle all errors from anywhere in the flow. Once again, user is handling the errors, from which they may recover.
4) Dont wire any failure terminals, tryCatch nodes, or Catch terminal of Input node... transaction is rolled back ... this instance of the flow ends. The message is then retrieved again in a new Unit of Work, and may retry, or may be propagated to the failure terminal of the input node, based on Backout counts
But 4 on its own isnt very useful, since the user doesnt examine the error
that was produced.
Since you can issue a THROW in (1) to (3), then you can use these to examine the error, and decide if it can be handle, or if you eventually want a transactional rollback. So you combine (1) - (4) to introduce inspective error handling into your flow, where you can choose to examine the errors, and/or rollback the transaction.
_________________
Regards
Craig |
|
| Back to top |
|
|
| Craig B |
| Posted: Fri Sep 19, 2003 12:05 pm Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Eddie,
To answer your question, issuing an ESQL THROW (or using a throw node) causes the same error handling logic to be driven as if the flow had generated an exception itself.
An attempt will be made to propagate to the failure terminal if it is wired (Obviously this only applies to the THROW statement, a Throw node doesnt have a failure terminal). If this is not wired, it will be propagated down the first TryCatchs Catch Terminal if it encounters one. If it does not find one, it will eventually come back to the MQInput node, and try and propagate to the Catch terminal if wired, else the transaction will be rolled back. If you had none of this error handling wired then this would lead to a roll back. However if your input message was not in the Unit of Work then it would look like it had been lost because it wont have been rolled back. If you have Transaction mode = Automatic in the MQInput node, then this retrieves with MQGMO_SYNCPOINT_IF_PERSISTENT. So if you send in a non-persistent message, then it will not be in the unit of work. Therefore, even if your flow rolls back. .... this message will not be rolled back because it was in Syncpoint. This could give the appearance that your message was lost.
_________________
Regards
Craig |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Sep 19, 2003 12:47 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
O.K. this really sucks, and obviously I forgot the role of rollbacks.
I have this problem them. We need to capture the error, but also have transaction rollback because of the large number of stored procedures being called within the flow. So, if one stored proc fails then all should fail, but at the same time as part of the error handling we are doing we are storing the error from the stored proc and putting that into a special table for review later on. Also if it fails we would also send it to an email node.
I haven't used stored procs before, let along calling multiple stored procs in one flow so I never saw this issue.
Any ideas, advice, vision
Chris  |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Sep 19, 2003 12:55 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
O.K. One of the developers added a Filter node and reviewed the return code from the stored proc. If the return code is not zero then the filter node would return false, otherwise it would return true. This from testing seems to do a transactional rollback.
Why is this unique?
Chris |
|
| Back to top |
|
|
| Craig B |
| Posted: Fri Sep 19, 2003 1:00 pm Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
When you say the stored procedure returns an error then what kind of error is it reporting? Did the database operation/update succeed in the first place. You may not be distinguishing between an operation that was successful and was rolled back, versus an error such that the update never took place in the first place, and so there was nothing to commit or rollback.
_________________
Regards
Craig |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Sep 19, 2003 1:10 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
| There are two levels of failure. The first one being the compute node threw a database exception due to a problem calling the stored procedure. The second which is the one I am referring to more so is when the stored procedure call succeeded, but the call is returning back a failure code in its OUT parameter. If the OUT parameter has a failed return code then a transactional rollback needs to occur. |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Sep 19, 2003 1:29 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
O.K. I tested the Filter node concept and it doesn't work for me so I will bow my head knowing that this really stinks 
Any suggestions on the above inquiry? |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Sep 22, 2003 8:18 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| cfryett wrote: |
| There are two levels of failure. The first one being the compute node threw a database exception due to a problem calling the stored procedure. The second which is the one I am referring to more so is when the stored procedure call succeeded, but the call is returning back a failure code in its OUT parameter. If the OUT parameter has a failed return code then a transactional rollback needs to occur. |
So within your compute node check for the return code and use THROW USER EXCEPTION statement to raise an error within your message flow. Please make sure your DB Transaction property is set to Automatic for this node.
Please make sure your path ends with a Throw node (or THROW statement) if any of the following is true,
a) your compute node's failure terminal or MQInput node's catch terminal is attached to "Error Processing" nodes.
b) you are using Try-Catch node before this compute node.
The objective here is to throw exception all the way back to the MQInput node so that it propagates via failure terminal and DB rollback occurs.
Now, If you are doing your error processing, such as inserting Data into Error table then that transaction will roll back as well. To avoid this you can either set the transaction mode to commit in your DB/Compute node or separate out that processing by putting a message on the queue (with transaction mode = commit) and let another message flow do the error table insert.
If this doesn't work then I would be interested in hearing your "Error Processing".
_________________
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 |
|
|
| EvolutionQuest |
| Posted: Tue Sep 23, 2003 7:36 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
O.K. I am confused.
One of my colleagues is working with my message flow and is getting rollback to occur with the failure terminal wired on the MQInput node. From my understanding in order to properly get transactional rollback no failure terminal can be wired and no Try/Catch node/terminal can be wired correct? So, what gives?
I have the transactional mode set to automatic and that has not changed!
So, I fully understand this in order to obtain transactional rollback the message needs to come back to the MQInput Node. If the catch terminal is wired the transactional rollback will not occur, but if the failure terminal is wired it seems to work fine. This seems to go against what Craig has mentioned above or I have mis-interpreted it.
Could you enlighten me please!
_________________
Don't blame technology, for technology was created by humans! |
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
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
|
|
|
|
