rj_khatri
asked on
Transaction management in db2
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.
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.
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
HtH
ASKER
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
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
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..
is it a "hard" database error --- duplicate rows etc,
or is a logical condition detected by your stored procedure..
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
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
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.
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.
ASKER
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.
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.
ASKER
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.
Hi!
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.
HtH
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
ASKER
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.
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
Best Wishes
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!