rollback stored procedures 1

[jjluna23]

Greetings.

I have an stored procedure that call others stored procedures. If one of them fails, i need all the process to rollback. Every stored procedure, in case of fail returns an exception, which is caught by the main sp. At this point it works well.

Then, when the exception is grabbed, i send a rollback statement, but all the changes made by the successful sp remains in the database.

Is there a way to rollback all the process?

Thanks in advance.

 

[MarcLodge]

Hi jjluna23
Have a look at this article here:

http://publib.boulder.ibm.com/infoc...pic=/com.ibm.db2.doc.apsg/bjnqdist1005169.htm

Have a look also at your COMMIT ON RETURN statement in the DB2 definition. It may be that this has been set up with a value of 'YES' which I believe will commit the sub stored procs as they exit. It may be that you require COMMIT ON RETURN NO as the definition.

Marc