CODEWITHSUNDEEP
stored-proceduresdb2db2-luw
Manoj
Manoj

Reputation: 31

How to store db2 procedure data in a table, if application rolls back that db2 procedure transaction?

I would like to know if there is any way for storing the db2 procedure data, in a table, even if that particular procedure has been rolled back by the application.

The application is doing an update statement in table1 and then calls the procedure proc1, which returns either N or Y in the output cursor. When the output of the procedure proc1 is N, then the application rolls back the transaction, which includes the running of db2 procedure and the update on the table 1. Is there any way of inserting a record in table_err, during running of procedure proc1, when the application rolls back the transaction due to the proc1 output been N?

The db2 version been used here is 11.1 LUW

Upvotes: 0

Views: 208

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Use so called autonomous procedures for this.
Calling procedures.

An autonomous procedure is a procedure that, when called, executes inside a new transaction independent of the original transaction. When the autonomous procedure successfully completes, it will commit the work performed within the procedure, but if it is unsuccessful, the procedure rolls back any work it performed. Whatever the result of the autonomic procedure, the transaction which called the autonomic procedure is unaffected. To specify a procedure as autonomous, specify the AUTONOMOUS keyword on the CREATE PROCEDURE statement.

Upvotes: 1

Related Questions