× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.