| Author |
Message
|
| venusboy |
 Posted: Tue Jan 31, 2006 12:48 pm Post subject: Mixing Coordinated and Uncoordinated db transactions |
 |
|
Acolyte
Joined: 11 Jun 2002
Posts: 51
|
I have a requirement to use coordinated and uncoordinated database transactions within the same node. I noticed that in WMB version 6 there is an ESQL Reserved word UNCOORDINATED. Therefore the following is valid ESQL:
INSERT INTO Database.{NonTransactionalDatasource}.{Schema}.Table UNCOORDINATED () VALUES () …
INSERT INTO Database.{TransactionalDatasource}.{Schema.}Table …
However the UNCOORDINATED is not documented within the ESQL WMB manuals (and I cannot remember if this was in version 5).
Does any one know:
a) If the above works
b) It is supported by IBM.
Thanks in advance. |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Tue Jan 31, 2006 8:49 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi venusboy,
I found the below in the Airline sample notes:
| Quote: |
| There are two databases in this sample, rather than just one, because in WebSphere MQ the same database connection cannot be used for both coordinated and uncoordinated transactions in the same message flow. In the Error Handler sample, the database update in the main message flow is under transactional control so that if an error occurs, the update rolls back and is not committed. The database update in the subflow is not under transactional control so that the update is always committed. As a result, the Error Handler sample requires two separate databases. |
Guess it does not for v5.
Have you tested your code, is it working ? if it does then I guess IBM is supporting it 
Regards. |
|
| Back to top |
|
|
| venusboy |
| Posted: Wed Feb 01, 2006 1:06 am Post subject: |
|
|
Acolyte
Joined: 11 Jun 2002
Posts: 51
|
Elvis,
Thanks for the reply. To give you a more clearer picture, I need to write a global function that will always commit to the database outside the transation control for audting purposes. I want to use this function within database nodes that will be under transactional control. I don't wish to have to use the Environment tree to store this audit information and then pass it into a subflow to do the commits. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Feb 01, 2006 1:56 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi venusboy,
U want the audit table to be updated irrespective of the message failing or passing through.
I dont think you can have both Automatic and committed transaction in one node itself....
you will have to put the global function into another database node and use commit on that.
Regards. |
|
| Back to top |
|
|
| venusboy |
| Posted: Wed Feb 01, 2006 2:05 am Post subject: |
|
|
Acolyte
Joined: 11 Jun 2002
Posts: 51
|
| Well if the UNCOORINDATED is option is not supported, then I will have to get each call to the function to create a row within an environment tree list and then on exit of the flow call a sub-flow to perform the commit. I just haven't got time to test it.. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Feb 01, 2006 3:55 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| elvis_gn wrote: |
| I dont think you can have both Automatic and committed transaction in one node itself.... |
People do this all the time, with error handling branches in their flows - usually setting the transaction mode on an MQOutput node to ensure that the error message gets written to the queue even though they are rolling back the input message.
Participation in the automatic transaction gets closed at the first node that uses an explicit transaction mode. So if you have an MQInput node, and a database node that use auto, and then you have a compute node that doesn't use auto, and another database node that uses auto - only the first two nodes are in the transaction.
At least, that's what I remember. Time for me to hit the books!
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| venusboy |
| Posted: Wed Feb 01, 2006 4:43 am Post subject: |
|
|
Acolyte
Joined: 11 Jun 2002
Posts: 51
|
Jeff,
Thanks for your input. You are right with your statement if you use the SAME datasource odbc connection. If you use two different datasource one for each type (tranactional and non-transactional) then it's fine (well it can be done) for different nodes.
All I was after if the UNCOORIDATED ESQL statement was supported in version 6. I did a inital test and it seemed to behave correctly. i.e.
INSERT INTO Database.{NonTransactionalDatasource}.{Schema}.Table UNCOORDINATED () VALUES () …
INSERT INTO Database.{TransactionalDatasource}.{Schema.}Table …
Throw Exception X
The end result there was one row in my table, but then I corrupted my toolkit so I could carry out any further tests. |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Feb 01, 2006 7:23 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
The ESQL keywords COMMIT, ROLLBACK and UNCOORDINATED have been reserved for furture use.
These keywords have not been implemented in v6 and are therefore unsupported.
_________________
Regards, Ian |
|
| Back to top |
|
|
| venusboy |
| Posted: Wed Feb 01, 2006 7:53 am Post subject: |
|
|
Acolyte
Joined: 11 Jun 2002
Posts: 51
|
Ian,
Thanks you for this.
I assume from your location that you work on the product so I regard this matter as closed.
However I would suggest that IBM make sure that the ESQL/Eclipse auto-complete does not display keywords that are not supported to avoid this type of confusion.
Thanks for all who replied.
Tom. |
|
| Back to top |
|
|
| Ian |
| Posted: Tue Feb 14, 2006 6:19 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Tom,
Thanks for the feedback here, I will do the necessary to get this addressed.
_________________
Regards, Ian |
|
| Back to top |
|
|
|
|
