I have a Stored Proc, which in turn calls 4 Stored Procs.

CREATE PROCEDURE ZAVERR.MAIN_PROC
SPECIFIC MAIN_PROC
LANGUAGE SQL

BEGIN

DECLARE YEST TIMESTAMP;

IF (YEST NOT IN (SELECT DATE FROM HOLIDAYTABLE)) THEN

INSERT INTO ZAVERR.MYTABLE (REPORTDATE, UPDATETIME)
VALUES (TIMESTAMP(CURRENT DATE, '00:00:00'), TIMESTAMP(CURRENT TIMESTAMP));

CALL ZAVERR.PROC_1;

CALL ZAVERR.PROC_2;

CALL ZAVERR.PROC_3;

CALL ZAVERR.PROC_4;

END IF;

I want to ensure that if any of the underlying procs fail, my main proc needs to rollback completely. i.e., if PROC_1 and 2 succeed and then PROC_3 fails, all the changes done in PROC_1 and 2 must be rolled back.

Each of the PROCS 1 through 4 have a few Insert statements inside them.

Can someone help with this.

Thanks.

This is the default behaviour ... (of course, assuming that there are no commit stmts in any of the procs).

You may want to consider using exception handlers to give a graceful exit message

Sathyaram

Thanks Sathyaram.

Just wondering. Aren't Stored Procs in UDB Auto-committed. Once PROC_1 is executed succesfully, will UDB not commit the changes when exiting the PROC_1?

AFAIK, They are not auto-committing ...

And in many cases (like, when using a transaction manager, eg. websphere), not encouraged to commit inside a SP

Thanks
Sathyaram

I tried this without any explicit commits. I introduced an error in PROC_1.

I would have expected that when PROC_1 throws an error, the insert statement in my MAIN_PROC should be rolled back.

But that did NOT happen.

Any ideas?

Thanks.

Any idea which platform and version you are running on ,,

Can you post a copy of the called procedures ?

Thanks
Sathyaram

Statements inside the SP are auto commitable by default.

If you want to make all the call statements as a single transaction,declare the compound statement with AUTOMIC(by default compound statement is NOT AUTOMIC).
CREATE PROCEDURE ZAVERR.MAIN_PROC
SPECIFIC MAIN_PROC
LANGUAGE SQL
BEGIN AUTOMIC
...
CALL ..
CALL ..
...
END
One more option is,use commits and rollbacks from your application side after calling the main procedure.

CALL ZAVERR.MAIN_PROC
IF RETURN_STATUS = 0 THEN
ROLLBACK
ELSE
COMMIT
END IF
You can depend on the RETURN_STATUS of the stored proc or on SQLCODE, SQLSTATUS values.

Thanks,

The SP's I have written in DB2 for LUW (Linux, UNIX, Windows) do not auto-commit by default, and I have coded commits and rollbacks as appropriate in the SP. But I am not using Websphere with these SP's.

I understand that commits are not allowed in z/OS SP's, but not sure about that. Unfortuneately, some of the posts above do not specify the OS platform and DB2 may not function exactly the same on all platforms in this regard.

For error handling in DB2 LUW sub-routines (SP called from another SP) you may need to use exception handling and pass the error status back to the calling SP, who can commit or rollback as appropriate.

I guess I should have explained before ..On all platforms. zSeroes, LUW and iSeries, "COMMIT ON RETURN NO" is the default (LUW, this is the only option).
Implicitly, to me this meant, the sql statements within the procedure are not committed automatically ..
About using COMMIT in the SPs, all platforms permit them , albeit with a few restrictions, like commit is not allowed for in procedure called by a trigger .. iSeries is the most restictive in this regard.

HTH

Sathyaram

I strongly believe the statements inside the SP's(DB2 for LUW ) are auto commitable by default.

I tested this behavior with the below statements.I created a table with a Primary key and trying to insert a duplicate record and was expecting -803 error.

CREATE TABLE TAB1(A INT NOT NULL,B CHAR(50));
ALTER TABLE TAB1 ADD PRIMARY KEY (A );

CREATE PROCEDURE TestSP ( )
P1 : Begin
INSERT INTO TAB1(A,B) VALUES (100,'sample');
INSERT INTO TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
End P1

Call <schema>.TestSP();
Error :
SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "<schema>.TAB2" from having duplicate rows for those columns. SQLSTATE=23505

Below is the output of SELECT * FROM TAB2;
A B
--- ---
100 sample

If the statements are not auto-commitable then the record(100,'sample') should not have inserted into the table.

Thanks,

Each individual statement in a SP is NOT autocommitted. What you are seeing is that the first insert statement is executing OK. The second one fails. Then, since you are using the CLP where autocommit is on by default, the call is then committed. This is what is saving the data in the first insert.
To prove this, I took your code and just added a condition handler to the SP. Here is the results:

create table andy.tab1(a int not null, b char(50))@
alter table andy.tab1 add primary key (a)@

CREATE PROCEDURE andy.TestSP ( )
P1 : Begin
declare continue handler for sqlexception rollback;
INSERT INTO andy.TAB1(A,B) VALUES (100,'sample');
INSERT INTO andy.TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
End P1 @
------------------------------------------------------------------------------
create table andy.tab1(a int not null, b char(50))
DB20000I The SQL command completed successfully.

alter table andy.tab1 add primary key (a)
DB20000I The SQL command completed successfully.

CREATE PROCEDURE andy.TestSP ( )
P1 : Begin
declare continue handler for sqlexception rollback;
INSERT INTO andy.TAB1(A,B) VALUES (100,'sample');
INSERT INTO andy.TAB1(A,B) VALUES (100,'Expecting a failure'); -- Duplicate record
End P1
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
call andy.testsp()@
------------------------------------------------------------------------------
call andy.testsp()

Return Status = 0


------------------------------ Commands Entered ------------------------------
select * from andy.tab1@
------------------------------------------------------------------------------
select * from andy.tab1

A B
----------- --------------------------------------------------

0 record(s) selected.


Andy

As Andy has mentioned, in your case, I think you have used db2’s command line to call the procedure, which by default is autocommit ..

So, your call was equivalent of

Call <schema>.TestSP();
Commit ;

I have approached the same issue of 'proving' the fact, from a slightly different angle ..

The compound statement in your procedure is NOT ATOMIC, which means an error in one of the statements will NOT undo the previous changes in the same compound stmt.

Use ‘Begin atomic’ instead of ‘’begin’ and you’ll see the difference.

Even better, for understanding, call your original sp with autocommit off ..

Db2 +c Call <schema>.TestSP();

When the error is returned, do a rollback ..

Db2 rollback

Now you will not see any data in the table … If the SP statements have been autocommit, then your rollback would have had no impact.

HTH

Sathyaram