| Author |
Message
|
| mnoohu |
 Posted: Tue Aug 14, 2012 4:51 am Post subject: WMB Transaction |
 |
|
Newbie
Joined: 14 Aug 2012
Posts: 9
|
Dear Experts,
This is my first entry into this great forum. Here are my problems seek experts advice.
We are continuously experiencing few issues mainly in WMB transaction management.
Different database connections are acquired for java compute nodes even though those are part of same main flow
a. Main flow has some ESQL compute nodes and a java compute node and calls two other subflows which also has java compute nodes
b. The java compute nodes in both main and subflows has its own CRUD operations.
c. It was observed that the object addresses of database connections created in all java compute nodes were not same.
d. So in this case, our business process couldn’t succeed because for second subflow, the new record created at first subflow was not visible.
Due to this inconsistency, the transactions were partially completed. Means some of the tables were get inserted/updated and some were not.
Additional inputs:
1. The transaction mode for MQInput node is selected as “Yes”
2. Separate ODBC DSN is used for the ESQL compute nodes having Automatic transaction status and Commit transaction status.
Please advice. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Aug 14, 2012 5:02 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Another way to look at it is: set MQInput transaction mode to off, and keep track of your changes. In your error handler, if you need to back out your changes, issue the appropriate commands to backout the changes.
Different SQL drivers can have different levels of transaction aware compatibility.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 14, 2012 5:36 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can't share connections between ODBC and JDBC.
You haven't said if you are using MBSqlStatement or getJDBCType4connection() in your JCN.
Remember that each EG is a standalone process with it's own JVM, so that all JCNs that run in the same EG have access to the same memory space. |
|
| Back to top |
|
|
| mnoohu |
| Posted: Tue Aug 14, 2012 6:21 am Post subject: |
|
|
Newbie
Joined: 14 Aug 2012
Posts: 9
|
Thanks for your replies.
I tried with no transaction but not succeeded.
I'm using getJDBCType4connection() in JCN. I'm not sharing connection between ODBC and JDBC
Lemme explain more about my scenario.
Main flow:
1. MQInput node (Transaction is set as 'Yes') receives an XML message
2. ESQL compute node (Transaction is set as 'Commit') updates database table as "Received"
3. ESQL node splits xml message (repeating elements) and propagates one by one
4. Calls a sub flow to validate the message
5. Routes to appropriate subflow depending upon the message type if validation was success.
6. The subflow(contains java compute node) creates records in three tables
7. This goes till end of repeating elements.
8. At the end of splitting, a java compute in main flow does following operations:
a. Updates master table with success counts and failure counts(validation status counts)
b. All failure records are inserted into error table with corresponding xml message (Retrieved from environment variable copied in validation sub flow) (Again it is through JDBC batch)
c. Then we query master table
d. Finally we trigger a confirmation message if success+failure equals total records count maintained in master table. (MQOutput node transaction mode is selected as 'Yes'
e. If the counts are not matched no action will be taken.
Here is the strange thing happens which makes us worry:
At the end of flow the transaction was committed only to point 6(subflow) and for 8 d.
For 8 a the update was not succeeded and for 8 b the records were not created.
When I checked for DB locks, it was found that both master and error tables were in locked state and it never released until I stop execution group.
My observation:
I tried printing jdbc connection object addresses and was found different for point 6(subflow) and for point 8. Why it so? Since I have marked transaction as 'Yes' all connection object address should be same throughout the flow.
If it is different how a record created in subflow A will be visible to subflow B?
Where is WMB transactional behaviour stands here?
I suspect myself first as I'm having only one year experience in WMB, experts please provide your valuable suggestions and advices. |
|
| Back to top |
|
|
| mnoohu |
| Posted: Tue Aug 14, 2012 6:24 am Post subject: |
|
|
Newbie
Joined: 14 Aug 2012
Posts: 9
|
| BTW, the flow is deployed to only one EG. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Aug 14, 2012 6:28 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
WMB transactions are 'simple'. Your trying to use transactionality in a complex way.
Complex transactions need custom code. In your Environment address space or in a separate queue, keep track of all your transactions. If some failure, and you need to roll back, have your error handler issue compensating transactions. Do not use the WMB transaction function; use your own transaction compensation.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 14, 2012 6:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
It seems reasonable that each time you call getJDBCConnection, you would actually get a new connection, doesn't it?
There's some basic database stuff here, like row level locking vs. table level locking, that some assistance from your DBA may help.
Again, if you want to use the same JDBC connection from more than one place in the same EG, you can just keep track of the connection object. |
|
| Back to top |
|
|
| McueMart |
| Posted: Tue Aug 14, 2012 6:40 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
This is an interesting thread. I have hit very similar issues to the OPs when accessing the same table in 2 different JCNs within the same flow. I had simply assumed that getJDBCType4Connection() would return the same instance of the connection for a particular message flow (thread), when transaction mode is yes.
The problems I observed were similar to the OPs: some transactions left inflight and only one of them commited.
What mqjeff is implying is that we should be keeping track within our own code for a particular connection object. This doesnt sound very neat at all. Can you clarify jeff? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Aug 14, 2012 6:46 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| McueMart wrote: |
This is an interesting thread. I have hit very similar issues to the OPs when accessing the same table in 2 different JCNs within the same flow. I had simply assumed that getJDBCType4Connection() would return the same instance of the connection for a particular message flow (thread), when transaction mode is yes.
The problems I observed were similar to the OPs: some transactions left inflight and only one of them commited.
What mqjeff is implying is that we should be keeping track within our own code for a particular connection object. This doesnt sound very neat at all. Can you clarify jeff? |
Aside from the workaround that mqjeff suggests, this points to a larger maturity issue within the product. In my opinion, the WMB product is not as mature as we need it to be here. In much the same way as DB2 connection pooling.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 14, 2012 6:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Aside from the workaround that mqjeff suggests, |
Is it a workaround to implement the message flow in a way that satisfies the business requirements given?
Any time you are doing anything where you are doing multiple inserts, selects, and updates from separate tables in the same database, you should be working with your DBA to identify the transaction boundaries. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Aug 14, 2012 7:53 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| lancelotlinc wrote: |
| Aside from the workaround that mqjeff suggests, |
Is it a workaround to implement the message flow in a way that satisfies the business requirements given?
Any time you are doing anything where you are doing multiple inserts, selects, and updates from separate tables in the same database, you should be working with your DBA to identify the transaction boundaries. |
Nonetheless, workaround or not, the user community is advocating for a more mature database interface in the WMB product. The current database nodes and database functions are a good start; we feel that more can be done to improve the product.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 14, 2012 9:29 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Nonetheless, workaround or not, the user community is advocating for a more mature database interface in the WMB product. |
I'm not advocating *against* anything.
I'm a little confused about the distinctions being drawn, and the expectations that are being expressed.
And I'm more than a bit confused that lancelotlinc isn't understanding that I'm suggesting a perfectly reasonable usecase for a Singleton pattern.
Nothing in the documentation on createJDBCType4Connection makes any claims that it will ever give you anything other than a "new" connection each and every time it is called. There's no reason to expect that if you call it twice against the same datasource in the same instance of a message flow that you will get the same connection back.
The JavaCompute node does not have a transaction mode in the same way that a Compute node does - so why would you expect that you can change how it makes database interactions?
Again, when making any kind of complicated database interactions - more than just a single database call - you should sit down and understand the transactional boundaries that are in effect and you should then map those boundaries to your message flow logic and the capabilities of the nodes you're going to use. It's called 'development'.
| lancelotlinc wrote: |
| The current database nodes and database functions are a good start; we feel that more can be done to improve the product. |
You know there is an established and formal procedure for creating a request for enhancement.
And always remember that there's nothing that prevents you from having more than one Datasource (ODBC or JDBC) that point to the SAME database. Even with DB2 client and the weird restrictions on the ODBC DSN name being the same as the name in the database catalog, you can always catalog the same database under different names. |
|
| Back to top |
|
|
| McueMart |
| Posted: Tue Aug 14, 2012 3:02 pm Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
| Quote: |
| Nothing in the documentation on createJDBCType4Connection makes any claims that it will ever give you anything other than a "new" connection each and every time it is called. There's no reason to expect that if you call it twice against the same datasource in the same instance of a message flow that you will get the same connection back. |
Maybe im interpreting it wrong but there are at least 2 pieces of information on this page http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac30494_.htm&resultof=%22getJDBCType4Connection%22%20%22getjdbctype4connect%22 which led me to believe that broker WAS doing something clever.
| Quote: |
| The broker manages the connections, thread affinity, connection pooling, and life cycle. |
Specifically the 'thread affinity' bit. And...
| Quote: |
| The only valid location for the getJDBCType4Connection call is in the JavaCompute node's evaluate method. Do not code the getJDBCType4Connection call in a JavaCompute node constructor in case the connection is lost and needs to be reestablished. SQL connections are cached by the broker and might be released because of inactivity, or if a communications failure with the database is detected. Therefore, make the getJDBCType4Connection call in the evaluate method, which returns the existing cached connection if it is available, or a new connection if it is not. |
Particularly the last sentence.
Hopefully you can see where my confusion (and possibly others?) arose from? (It wasn't through lack of 'development' or knowledge of transactional boundaries  )
If you could clear up what the infocenter means by these that would be apprciated. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 14, 2012 3:51 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| McueMart wrote: |
| Quote: |
| Therefore, make the getJDBCType4Connection call in the evaluate method, which returns the existing cached connection if it is available, or a new connection if it is not. |
Particularly the last sentence. |
Yes, okay.
| McueMart wrote: |
Hopefully you can see where my confusion (and possibly others?) arose from? (It wasn't through lack of 'development' or knowledge of transactional boundaries )
If you could clear up what the infocenter means by these that would be apprciated. |
Well, I can't say that I can particularly clear up what the Info Center means.
I can however, draw attention to the word 'available', and suggest that it is being used with a deeper and more specific meaning than might otherwise be understood at first glance. There are plenty of reasons why a connection used from a main flow that has done an insert might be considered 'unavailable' to a connection request inside a subflow.
There's also the possibility that the 'availability' is tied in part to things like the name of the class making the call.
Again, I don't know any of this for *certain*. I am merely speculating.
And your link provides some solid discussion of why you might use MBSqlStatement instead of getJDBCType4Connection, too. |
|
| Back to top |
|
|
| mnoohu |
| Posted: Fri Aug 31, 2012 10:43 am Post subject: |
|
|
Newbie
Joined: 14 Aug 2012
Posts: 9
|
I had to spent some days to find the root cause with different scenarios why transactions didn't succeed as we expected.
I think our assumption regarding a unique database connection object is used throughout a flow process within different JCN was totally wrong.
As said by mqjeff, it is reasonable that we get new connection every time when we call getJDBCConnection within different JCNs. And this was the
reason our subflow couldn't find the newly created(within main flow) record in the database.
Going further, to find why some transactions were committed and why some were not and why some tables were locked.... here is the findings.
In my scenarios,
The first database connection is obtained in scenario 6 where first record getting created.
The update and create operations at scenarios 8a and 8b were not committed because it has its own DB connection(totally new)
It was observed that WMB performs commits at the end of flow only for the first JDBC connection object and not for subsequent connections obtained in other JCNs.
That was the reason scenarios 8a and 8b left uncommitted and was locked as well.
As a work around proposed by mqjeff, with singleton pattern approach by simply storing and retrieving connection object from the memory space gave successful result as we expected. All update and create operations were succeeded and no table got locked.
The question again hits my mind that, where WMB maintains transaction throught the flow when it said transaction starts at beginning of flow and ends when flow returns?
If WMB couldn't manage transactions for multiple JDBC connections within a flow, it should have explained users not to go with different JDBC connections.
Since we have already tied with this product and it is not that easy decision to switch to another, experts please clarify,
Is the singleton pattern recommended and best solution we can rely on?
BTW we are using WMB 8.0.0.1.
Last edited by mnoohu on Fri Aug 31, 2012 10:53 am; edited 1 time in total |
|
| Back to top |
|
|
|
|
