10-23-2003, 04:58 AM
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.