V5R2 ATOMIC Stored Procedure Change

From: http://www-1.ibm.com/servers/eserver/iseries/db2/v5r2atomic.html

For those unable to reach the web, and for the archives, here is the IBM
page:
V5R2 ATOMIC Stored Procedure Change

In pre-V5R2 releases, SQL stored procedures that contained atomic
compound-statements automatically issued a Rollback statement if the
procedure encountered any errors during execution and a Commit statement if
the procedure executed successfully. For example, if the Delete statement
failed in the V5R1Procedure below, a Rollback statement would be issued by
DB2 and both the Insert & Delete statement would be rolled back. If both the
Insert & Delete statement were successful, a Commit statement would be
executed at the end of the stored procedure on pre-V5R2 systems.

CREATE PROCEDURE V5R1Procedure(IN parm1 INT)
LANGUAGE SQL
BEGIN ATOMIC

  INSERT INTO t1 VALUES (parm1,'A');
  DELETE FROM t2 WHERE c1=33;

  END


The implementation of atomic compound-statements has changed in V5R2.
However, this implementation change will not affect SQL procedures until
they are recreated on a V5R2 system. Any existing atomic SQL stored
procedure will continue to run as expected until that procedure is recreated
on a V5R2 system. The V5R2 atomic implementation change requires the
programmer to either add a COMMIT ON RETURN YES clause to their SQL stored
procedure definition or change the invoking application to be responsible
for committing or rolling back any changes made by the stored procedure.

The source for the V5R2Procedure shows how to add the COMMIT ON RETURN YES
clause to maintain the pre-V5R2 behavior of an atomic SQL stored procedure.

CREATE PROCEDURE V5R2Procedure(IN parm1 INT)
LANGUAGE SQL
COMMIT ON RETURN YES
BEGIN ATOMIC

  INSERT INTO t1 VALUES (parm1,'A');
  DELETE FROM t2 WHERE c1=33;

END


A good resource for learning more about SQL Stored Procedures is the "Stored
Procedures and Triggers on DB2 UDB for iSeries" redbook which can be found
online at: www.ibm.com/redbooks.