| Author |
Message
|
| andrewfemin |
 Posted: Thu Nov 09, 2017 3:00 am Post subject: DB2 Rollback Issues in IIBv9 |
 |
|
Acolyte
Joined: 26 Aug 2017
Posts: 54
|
Hi,
I have a message flow that inserts into two different databases from a single compute node. And further down in the compute node, sometimes an error is thrown.
| Code: |
PASSTHRU '<insert statement1>' TO Database.TESTDB1;
PASSTHRU '<insert statement2>' TO Database.TESTDB2;
.
.
.
.
.
.
.
.
.
.
.
THROW USER EXCEPTION MESSAGE 4017 VALUES ('User Exception');
|
The Transaction property of the compute node is set to Automatic and the message flow is transactional.
With this setting, I expected both the insert statements to be rolled back when the User Exception is thrown. But that did not happen. Only the insert statement made to TESTDB1 got rolled back and the other insert statement stayed in the table.
I tried giving the insert statements in reverse order as well, thinking this might have something to do with the opening and closing of DB connections.
| Code: |
PASSTHRU '<insert statement2>' TO Database.TESTDB2;
PASSTHRU '<insert statement1>' TO Database.TESTDB1;
.
.
.
.
.
.
.
.
.
.
.
THROW USER EXCEPTION MESSAGE 4017 VALUES ('User Exception');
|
Even in this case, only the insert statement made to TESTDB1 got rolled back. Both TESTDB1 and TESTDB2 are DB2 Databases and both have Auto-Commit turned ON. (I've checked using db2 list command options. Not sure if there's any other way to check. Please guide me)
Please let me know if this is expected behavior. When I throw the error, I want both the entries to be rolled back. The error thrown does reach the Input Node and the catch terminal is not connected. |
|
| Back to top |
|
 |
| vijragem |
| Posted: Mon Nov 13, 2017 4:01 am Post subject: Re: DB2 Rollback Issues in IIBv9 |
|
|
Novice
Joined: 09 Jan 2015
Posts: 14
|
If the transaction has to be rolled back, the transaction must be actually ended up as exception. If you have wired your input node catch and failure terminal and handled the exception, push it to a queue, then, transaction ended successfully after the commit. Hence, rollback is not happening even you kept your compute node transaction mode to automatic.
You have two options
1) Use compute node after your exception handler subflow(or whatever) where you have Rollback statement.
2) Use throw node after your exception handler subflow(or whatever).
Since you are using multiple DSN here, you handle that part in Rollback options instead of Throw node.
Make Sense!!!!!!!!!!!!!!!! |
|
| Back to top |
|
|
| andrewfemin |
| Posted: Sun Nov 19, 2017 10:50 pm Post subject: Re: DB2 Rollback Issues in IIBv9 |
|
|
Acolyte
Joined: 26 Aug 2017
Posts: 54
|
| vijragem wrote: |
If the transaction has to be rolled back, the transaction must be actually ended up as exception. If you have wired your input node catch and failure terminal and handled the exception, push it to a queue, then, transaction ended successfully after the commit. Hence, rollback is not happening even you kept your compute node transaction mode to automatic.
|
The flow has actually ended up as exception. Catch terminal of input terminal is not wired. Rollback is happening in one DB, not in another.
| vijragem wrote: |
You have two options
1) Use compute node after your exception handler subflow(or whatever) where you have Rollback statement.
2) Use throw node after your exception handler subflow(or whatever).
Since you are using multiple DSN here, you handle that part in Rollback options instead of Throw node.
Make Sense!!!!!!!!!!!!!!!! |
I have used Rollback statement for now. My question is, why is one DB getting rolled back but not the other? |
|
| Back to top |
|
|
| andrewfemin |
| Posted: Fri Nov 24, 2017 1:35 am Post subject: |
|
|
Acolyte
Joined: 26 Aug 2017
Posts: 54
|
|
| Back to top |
|
|
| abhi_thri |
| Posted: Fri Nov 24, 2017 6:11 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
You could check for differences between the odbc stanza used for the two databases...also may be it worth enabling odbc trace for both connections and compare it.
Your expectation is correct, ideally the rollback should happen for both databases. If all the settings are the same then may be it is time for a PMR... |
|
| Back to top |
|
|
| zpat |
| Posted: Fri Nov 24, 2017 7:59 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
Just a reminder: Transaction mode automatic means it varies based on message persistence.
So always test with persistent messages (RFHUTIL has the dangerous default of non-persistent).
_________________
Well, I don't think there is any question about it. It can only be attributable to human error. This sort of thing has cropped up before, and it has always been due to human error. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Nov 24, 2017 11:40 am Post subject: Re: DB2 Rollback Issues in IIBv9 |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| andrewfemin wrote: |
I have a message flow that inserts into two different databases from a single compute node.
| Code: |
PASSTHRU '<insert statement1>' TO Database.TESTDB1;
PASSTHRU '<insert statement2>' TO Database.TESTDB2; |
|
Which data source does the compute node use? What happens if the message flow uses separate compute nodes for each PASSTHRU statement, with the corresponding data source configured for each compute node?
| andrewfemin wrote: |
| Code: |
| THROW USER EXCEPTION MESSAGE 4017 VALUES ('User Exception'); |
|
BIP4017 is not the best choice here; it means something else. The broker reserves exception numbers from 2949 to 2999 for user exceptions generated from THROW statements. |
|
| Back to top |
|
|
|
|
