| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle Update Not happening using PASSTHRU |
« View previous topic :: View next topic » |
| Author |
Message
|
| tonythomasantony |
 Posted: Wed Aug 28, 2013 4:28 am Post subject: Oracle Update Not happening using PASSTHRU |
 |
|
Apprentice
Joined: 12 Aug 2006
Posts: 47
|
Hi ,
Am using WBIMB v8.0.02 in production.
I have a simple flow. MQInput>Audit>ComputeNode.
In the ComputeNode, i have 2 SQL operations . INSERT and UPDATE.
At a time, only one SQL will execute. Mean to say, when message flow picks up a message from the queue either INSERT or UPDATE. This INSERT or UPDATE will be decided by the content of the message. One more thing is the UPDATE will happen to the record which INSERT statement have inserted in to the DB.
Now the problem am facing is, the UPDATE not happening for the particular record even though there is more than 1 second gap in the UPDATE statement.
I am using PASSTHRU for the INSERT or UPDATE and also using explicit COMMIT using PASSTHRU. And also, in the compute node i have selected Transaction as Commit.
This issue is not happening for all the messages. But 5% of the transactions are affecting due to this problem.
If any one have experienced this problem kindly advice me how to solve this.
Regards,
Tony Thomas |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Aug 28, 2013 4:30 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
If you use passthru, you bypass the transactional integrity of the WMB infrastructure.
Why are you using passthru? Why not the native INSERT and UPDATE commands within ESQL ?
When you say you are explicitly using 'COMMIT' does that mean you have a 'COMMIT' ESQL command after the passthru ? (Note: this has no effect on passthru statements).
The database may reject an INSERT or an UPDATE for a particular reason, for example, violation of constraint criteria. How are you capturing that error? How does your code handle that error ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 28, 2013 5:11 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| If you use passthru, you bypass the transactional integrity of the WMB infrastructure. |
This is not correct - PASSTHRU is an integral part of the WMB transactional infrastructure - any statements issued using PASSTHRU are committed or rolled-back in exactly the same way as the other built in DB operations (SELECT, INSERT etc.).
| Quote: |
| Why not the native INSERT and UPDATE commands within ESQL ? |
This is a good question - I would recommend using the built in commands unless there is a reason to not use them (such as needing a more dynamic
statement, which is probably the case here). That said, as long as you use parameter markers (?) in your PASSTHRU statement, there should be no noticeable difference between the two.
| Quote: |
| This issue is not happening for all the messages. But 5% of the transactions are affecting due to this problem. |
Have you checked to see if an exception is thrown for one of these failing statements? Can you isolate one of the statements and have it repeatedly fail when you run it on a test flow?
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Last edited by mgk on Wed Aug 28, 2013 6:19 am; edited 1 time in total |
|
| Back to top |
|
|
| tonythomasantony |
| Posted: Wed Aug 28, 2013 6:14 am Post subject: |
|
|
Apprentice
Joined: 12 Aug 2006
Posts: 47
|
Hi mgk,
I have replayed the same message for Update. It worked.Likewise I have replayed 10-15 failed updates.All updated in db.
Is there is any way in esql to find out the number of records updated in db after update operation?
Regards,
Tony |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Aug 28, 2013 7:02 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| tonythomasantony wrote: |
Hi mgk,
I have replayed the same message for Update. It worked.Likewise I have replayed 10-15 failed updates.All updated in db.
Is there is any way in esql to find out the number of records updated in db after update operation?
Regards,
Tony |
SO your INSERT failed, but the UPDATE succeeded ? Does that mean the primary key already existed ? Should you be doing UPSERTs instead ?
@mgk, ok, I'll accept your statement that passthru honors the WMB transactionality mechanism; however, many previous discussions revolved around the need to provide database compensation logic in error handler due to the fact that passthru did not. I'll have to revisit those discussions to see the actual details.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| 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
|
|
|
|
