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 procedure’s unit of work.

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.

Restriction: You cannot include COMMIT or ROLLBACK statements in a stored procedure if any of the following conditions are true:
  • The stored procedure is nested within a trigger or user-defined function.
  • The stored procedure is called by a client that uses two-phase commit processing.
  • The client program uses a type 2 connection to connect to the remote server that contains the stored procedure.
  • Db2 is not the commit coordinator.
If a COMMIT or ROLLBACK statement in a stored procedure violates any of these conditions, Db2 puts the transaction in a must-rollback state. Also, in this case, the CALL statement fails.