Transaction management in db2

Dear Friends,

I have an application in c#(windows application on ) with DB2 database(that is on AS400). The client interface class uses a dataaccess class to interact with the database. The application runs 3 stored procedures one by one calling the functions of the dataaccess class that inserts the data in 3 tables.
Now the problem is to manage the transaction in all the 3 procedures i.e. if 3rd procedure fails then the execution of previously run 2 procedures should also be rolled back.

I am using "DB2OLEDB" provider. I have the M/S Host Integration server installed. I am using transaction object and command object to execute DB2 stored procedures.

Can you please help me to resolve the issue.

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi rj_khatri,
would it be simple to just create a procedure which invoked the other 3 procedures from
the UDB side of things and let that handle the transaction rollbacks  as required?

Using can you have begin transaction <transaction_name> and then call the three stored procedures. Have all of this in try catch block and then if you have any error have the catch block to rollback transaction name. CHeck this I dont know whether this will work but definitely you can give a try..

rj_khatriAuthor Commented:
Dear LowFatSpread,
As it has a lot of parameters so it is not feasible to pass these to a single procedure and then invoke each of three.

Dear askanivg
I am using ADO.Net connection and transaction objects and assign this transaction object to the three command objects. These command objects executes the procedures.
But it is not rolling back the modifications made in case of an error.

Is it something due to DB2???

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

what sort of "failure" are you recieving?
is it a "hard" database error --- duplicate rows etc,
or is a logical condition detected by your stored procedure..

Sorry for the late reply..
But can you try doing this for your problem.

Basically you say that the stored procedure ends in error so can you try handling the error code in the stored procedure itself and not in .Net.

Best way to handle is :

Have an output parameter for each stored procedure.
In the sotred procedure, if you have any errors, then handle the same in the sp itself. Do the rollback within the SP itself.

With the output parameter you can find out whether the sp has executed succesfully or not. If success continue executing the next stored procedures and follow the same approach.
rj_khatriAuthor Commented:
Dear askanivg,

Sorry for late reply.

Your suggestion for output parameter is ok but what if my second procedure fails.
Say I need to execute 3 procedures in sequence and execution fails at second procedure, In that case i will rollback all the modifications done by second procedure and avoid the execution of third procedure too. But I will not be able to rollback the modifications done by very first procedure.

rj_khatriAuthor Commented:
Dear LowFatSpread,

It can be any error. Say if execution fails just because of time out then too I need to rollback the modifications.
Can you try calling the second procedure in first procedure and also call the third procedure in second procedure. In this you will have the return codes for each stored procedures and you can handle the rollback in the first second and also third procedures.

Forgot .. follow the above procedure but before you proceed do

update commit off...

incase of success then have transaction object to commit and in case of failure then having the transaction object to do the rollback.

This way you can commit only once when all the three procedures have successfully executed and rollback if one of the three procedures has failed.


rj_khatriAuthor Commented:
I checked with the simple insert statements and a combination of both.
In both cases it rollbacks the modifications done by simple insert statement but the modification made by stored procedure are not rolled back.
So considering the result of above combination of stored procedure and simple insert statement, is it the feature of stored procedure that is causing the problem?

Dear askanivg
I will do, as you suggested, next.
Please try having the rollback in the stored procedure. Usual way of handling is that for simple insert statements we can do rollback in the code (.Net Code) and for stored procedures we generally handle the rollback in the sp itself and not in code. need to do it in DB2.

Best Wishes
rj_khatriAuthor Commented:
Yes, but for the example of 3 procedure (above)
All three are executed as a consequence of a button click.
I can rollback second procedure. But how to rollback first procedure's work within the second procedure. If there is a way Can you please explain.

And it is limitation with application that I can not call second procedure from the first procedure.
See basically on the click of button call the first procedure.
It should work fine. Do the following -
First have an extra out parameter in the stored procedure which returns the return code(SQL Code).
At the start of stored procedure have update command options c off; which does not commit unless a commit command is issued. Then have begin trans command. Check the SQL code in the stored procedure and then if success then use commit or else use rollback.

Now if the above thing is working, have the call to the second procedure from the first procedure. Remember one more thing that the second procedure should return the SQL code. Second procedure should also have update command options using c off and begin transaction.  By checking the SQL code (return code of the second procedure) the above things mentioned for first procedure should be followed. i.e. the first procedure should not be commited if the sql code for the second procedure is failed and if success commit the first stored procedure. The second procedure should have check for the sql code and if success then do the commit and return the success code to the calling procedure (i.e. first procedure).

Likewise you can call the third procedure in the second procedure. Try implementing first procedure once and then call the second procedure in the first procedure and then finally you can try calling the first procedure which in turn calls the second procedure which in turn calls the third procedure.

Best of Luck.. This should work and i have already confirmed the same with my DBA.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.