COMMIT and ROLLBACK statements in a stored procedure

When you issue COMMIT or ROLLBACK statements in your stored procedure, Db2 commits or rolls back all changes within the unit of work.

For procedures that are not defined as autonomous, the committed or rolled back changes include changes that the client application made before it called the stored procedure and Db2 work that the stored procedure does. For autonomous procedures, the committed or rolled back changes include only work done by the stored unit of work for the stored procedure.

If your stored procedure includes COMMIT or ROLLBACK statements, define it with the one of the following clauses:

• CONTAINS SQL
• READS SQL DATA
• MODIFIES SQL DATA

The COMMIT ON RETURN clause in a stored procedure definition has no effect on the COMMIT or ROLLBACK statements in the stored procedure code. If you specify COMMIT ON RETURN YES when you define the stored procedure, Db2 issues a COMMIT statement when control returns from the stored procedure. This action occurs regardless of whether the stored procedure contains COMMIT or ROLLBACK statements.

If you specify AUTONOMOUS when you define the stored procedure, the autonomous procedure is a separate unit of work from the calling application. Db2 issues a COMMIT statement when control returns from the stored procedure, but only changes completed by the autonomous procedure are committed. Similarly, COMMIT or ROLLBACK statements in the autonomous procedure code also have no effect on work done by the calling application.

A ROLLBACK statement has the same effect on cursors in a stored procedure as it has on cursors in stand-alone programs. A ROLLBACK statement closes all open cursors. A COMMIT statement in a stored procedure closes cursors that are not declared WITH HOLD and leaves open those cursors that are declared WITH HOLD. The effect of COMMIT or ROLLBACK on cursors applies to cursors that are declared in the calling application and to cursors that are declared in the stored procedure.