ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DB2 Rollback Issues in IIBv9

Post new topic  Reply to topic
 DB2 Rollback Issues in IIBv9 « View previous topic :: View next topic » 
Author Message
andrewfemin
PostPosted: Thu Nov 09, 2017 3:00 am    Post subject: DB2 Rollback Issues in IIBv9 Reply with quote

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
View user's profile Send private message
vijragem
PostPosted: Mon Nov 13, 2017 4:01 am    Post subject: Re: DB2 Rollback Issues in IIBv9 Reply with quote

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
View user's profile Send private message
andrewfemin
PostPosted: Sun Nov 19, 2017 10:50 pm    Post subject: Re: DB2 Rollback Issues in IIBv9 Reply with quote

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
View user's profile Send private message
andrewfemin
PostPosted: Fri Nov 24, 2017 1:35 am    Post subject: Reply with quote

Acolyte

Joined: 26 Aug 2017
Posts: 54

Back to top
View user's profile Send private message
abhi_thri
PostPosted: Fri Nov 24, 2017 6:11 am    Post subject: Reply with quote

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
View user's profile Send private message
zpat
PostPosted: Fri Nov 24, 2017 7:59 am    Post subject: Reply with quote

Jedi Council

Joined: 19 May 2001
Posts: 5849
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
View user's profile Send private message
rekarm01
PostPosted: Fri Nov 24, 2017 11:40 am    Post subject: Re: DB2 Rollback Issues in IIBv9 Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DB2 Rollback Issues in IIBv9
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.