| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: Mixing Mapping Nodes & Compute Nodes in the same f |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Mon Aug 04, 2014 10:00 am Post subject: IIB9: Mixing Mapping Nodes & Compute Nodes in the same f |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
In one of our message flows, we have a mapping node followed by a compute node, the mapping node is being used to 'also' insert into a database table, and the compute node that follows this then calls a stored procedure which at some point updates the same database table (in-fact the same row that was inserted).
The mapping node only supports Automatic Transactions, so the row that was inserted is locked by the JDBC session, this prevents the compute node from updating the row (since that's a separate ODBC session)..
Can I do something to make commit immediately after the mapping node (a commit inside a JCN)? or do I split this into separate flows & let the transaction be managed by the broker.. ( I suppose I could send a MQ message after the mapping node, which is picked up by a different flow to do the compute ), or do I write a JCN instead of ESQL to update the table.
Also, the stored procedure does a commit within its code.. (the JCN documentation says that transactions need to be auto and no explicit commit should be specified).
The scenario is essentially to create a facade for a file upload ( the broker is publishing an API, while the flow inserts into a database table, and executes a procedure to process the newly inserted row , something like simulating a file upload, and returns the result..
_________________
Regards |
|
| Back to top |
|
 |
| martinb |
| Posted: Tue Aug 05, 2014 1:42 pm Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Hi
Both the Mapping node (post v8 Graphical Data Mapping) Database Transforms and the Broker managed JDBC Connections that can be obtained by calling
| Code: |
| getJDBCType4Connection |
in a Java Compute node, JCN, use the "Broker JDBCProvider for type 4 connections" layer. This layer currently only supports
| Code: |
| JDBC_TransactionType.MB_TRANSACTION_AUTO |
which as you've said means the Database operation is only committed, or rolled back, when the whole flow transaction completes.
Hence you cannot perform sequential transactions on the same Database object from multiple Mapping or JCN nodes in a single flow transaction.
The ODBC connection from ESQL Compute node does offer "Commit" transaction mode, meaning the Database operation is committed purely on the node completion, regardless of the overall flow transaction outcome. You can also create JDBC connections directly not using the "Broker JDBCProvider for type 4 connections" layer, which would give you access to call commit / rollback. However that then means you have provide the connection details in your code rather than via the JDBC Provider configurable service, and you lose the connection polling and JDBC resource stats features.
Also remember if you do take an approach of managing the transaction within the node, you have to think about rollback / compensation if the overall flow fails.
As you've said the way to achieve these sequential transactions in the Database is by having the Map Insert and subsequent Stored proc update invoked from separate flows that are invoked in sequence, using MQ or other transport to couple them.
Another option you might consider is could you build a single stored proc that does the insert and process so that the Broker is only invoking a single unit? However I would imaging you have multiple rows to insert which would likely not fit well in this approach.
HTH |
|
| Back to top |
|
|
| akil |
| Posted: Tue Aug 12, 2014 8:54 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Thank you for the hint, will try with multiple flows coupled by a transport.
Could you reconfirm the following for me ,
1. these have to be completely different flows, sub-flows cannot be used, since the transaction management is at the flow level (including all its sub-flows)
2. I could use the a single flow such as the following
(mq) --> (compute) --> (mapping) --> (mq)
|
--> (esql compute)
by the above I mean I could code a single flow that is invoked recursively.
_________________
Regards |
|
| Back to top |
|
|
| martinb |
| Posted: Tue Aug 12, 2014 11:25 pm Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Hi
Correct a subflow runs within the same transaction / thread as the main flow.
I'm not sure I follow your recursive single flow. I think you are asking if you have multiple connections on the output terminal of the input node. In that case there will still only be a single transaction that spans the propagation down each branch of the flow. So you could not do your insert and then update the inserted data.
You need the two flows with separate input nodes to decouple your two stage transaction.
Another point I'd like to highlight in regards to a single flow using both ODBC (from ESQL) and JDBC (from JCN / Mapping node) is that you cannot do global (XA) co-ordination with both ODBC and JDBC. |
|
| Back to top |
|
|
| akil |
| Posted: Wed Aug 13, 2014 6:12 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Yes, I am aware that I can't use XA across ESQL & GDM.
By recursive I mean, I will have a single flow like follows
| Code: |
(mq input) ----> (route) ----> (gdm) ---> (mq output)
----> (esql - call procedure ) -----> (mq reply)
|
-- the flow has a route that either calls the GDM or the procedure call depending on a msg
-- the first time, the GDM will be called, and it'll alter the message to indicate that its got completed and it'll post a message to (its own) queue
-- the second time around, the route will go to the ESQL which will call the procedure..
I have to do this across some 5-10 tables , hence the above thought...
I suppose the flow commits when the ma-output is written , and before the next message is picked up from the queue ...so this should work , right?
_________________
Regards |
|
| Back to top |
|
|
| martinb |
| Posted: Wed Aug 13, 2014 8:11 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
So yes it achieves the transaction decoupling, the commit occurs when the flow completes the the MQOutput and the thread returns control back up to the MQ Input.
I'm sure there are plenty of better qualified people in the community to give a view on this "post it back to my own input queue" approach, but to me the concerns would be
- by default queue is a FIFO, so your part completed decoupled transaction goes to the end of the queue
- monitoring and end to end message transaction tracking becomes, well hard to follow
- the scope for a logic flaw to cause endless processing
- .. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
