Solved

Transaction management in db2

Posted on 2003-11-24
17
887 Views
Last Modified: 2013-12-03
Dear Friends,
Hi!

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  ADO.net transaction object and command object to execute DB2 stored procedures.

Can you please help me to resolve the issue.

Thanks in advance.
0
Comment
Question by:rj_khatri
  • 7
  • 5
  • 2
17 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9812625
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?


Cheers!
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9812673
Using ADO.net 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..

HtH
0
 

Author Comment

by:rj_khatri
ID: 9816678
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 ADO.net 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???

Thanks
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9816790
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..
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9825411
Khatri,

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.

HtH
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9825474
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.
0
 

Author Comment

by:rj_khatri
ID: 9825816
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.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rj_khatri
ID: 9825826
Dear LowFatSpread,
Hi!

It can be any error. Say if execution fails just because of time out then too I need to rollback the modifications.
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9825829
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.

0
 
LVL 2

Expert Comment

by:askanivg
ID: 9825865
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.

HtH



0
 

Author Comment

by:rj_khatri
ID: 9826451
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.
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9826476
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
0
 

Author Comment

by:rj_khatri
ID: 9826519
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.
0
 
LVL 2

Accepted Solution

by:
askanivg earned 100 total points
ID: 9826570
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.

HtH
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need a font Adobe Garland 4 97
Leveraging AD Groups in DB2 1 76
iSeries DB2 - Query with Sub Query? 7 98
erros on link  checking 2 42
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now