DB2 exception handling with rollback running simple scripts

[nps.fe...@gmail.com]

Hi all,


I've got some scripts that i want the ensure that runs without any error, and if any error occur the script rollback immediately.
I'm a newbie in DB2, but I've got something similar in MS SQL Server, something like this:

BEGIN TRANSACTION
BEGIN TRY

<SCRIPTS INSIDE>

COMMIT
PRINT 'COMMITED'
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'ROLLED BACK'
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

But in DB2 there's nothing like this.
I've tried to use SAVEPOINT and DECLARE EXIT HANDLER FOR SQLEXCEPTION but i got always error running this kind of script.
Errors:

An unexpected token "<space>" was found following "EXIT". Expected tokens may include: "HANDLER".. SQLCODE=-104, SQLSTATE=42601

Anyone have a example script that does what i want to do?

Cheers,

Bigster

[vvm...@gmail.com]

I recommend to use something like that:

db2 -f inputfile.sql -v -td@ -c- -s -m -q -l outputfileL.txt -r outputfileR.txt -z outputfileM.txt

see "CLP options" at https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010410.html?pos=2

You need
-c- (or +c) and -s
but other options also useful.

Input file contains something like
-- <SCRIPTS INSIDE>
statement1 @
statement2 @
COMMIT @
ECHO 'COMMITED'
@

No "BEGIN TRANSACTIONS", "TRY", "CATH" etc. You can control flow inside procedures, functions, triggers, but CLP scripts is very limited.

[nps.fe...@gmail.com]

Hi,

Thanks for your input.
The only problem is that i must run the script inside a IDE (Data Studio) and the script you mention in to invoke in the CMD line.
Like the script (in SQL Server) that i posted, i want to run it in the Database IDE.

Any ideas how to achieve that?


Cheers,

Bigster

[Mark Barinstein]

Hi,

See the example below:

--#SET TERMINATOR @
--create table test (i int)@
delete from test@

begin atomic
insert into test values 1;
signal sqlstate '75000' set message_text='Exception 1';
end@

commit@

begin
-- Exit handler needed for DB2 CLP - it rollbacks a failed statement automatically
-- if you want to have an ability to commit the 1-st insert
--declare exit handler for sqlexception begin end;
insert into test values 2;
signal sqlstate '75000' set message_text='Exception 2';
end@

commit@

begin
declare exit handler for sqlexception
begin
ROLLBACK TO SAVEPOINT sp1;
end;
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
insert into test values 3;
signal sqlstate '75000' set message_text='Exception 3';
end@

commit@

select * from test@

Regards,
Mark

[nps.fe...@gmail.com]

Hi Mark,

Thanks for your input.
Your approach works but only with simple scripts, say inserts, updates etc..., but when i put a CREATE TABLE it fails.

This is my example and the error:


BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
ROLLBACK TO SAVEPOINT sp1;
END;

SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;

-- Create Tables
CREATE TABLE CONFIG (
CONFIG BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
CLIENT_ID BIGINT NOT NULL);

signal sqlstate '75000' set message_text='Exception 3';

END;

Error:

An unexpected token "<ddl-statement>" was found following "". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.69.49
An unexpected token "<ddl-statement>" was found following "". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.69.49


Any idea why?


Cheers

[Mark Barinstein]

Hi,

You are not allowed to run static DDL statement in an anonymous compound statement like this.
You must use dynamic one instead:

--#SET TERMINATOR @
BEGIN
DECLARE STMT VARCHAR(4000);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK TO SAVEPOINT sp1;
END;

SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;

-- Create Tables

SET STMT=
'

CREATE TABLE CONFIG (
CONFIG BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
CLIENT_ID BIGINT NOT NULL)

'
;
PREPARE S1 FROM STMT;
EXECUTE S1;

signal sqlstate '75000' set message_text='Exception 3';

END@

[Mark Barinstein]

Or instead of SET=...; PREPARE ...; EXECUTE ...; just:

EXECUTE IMMEDIATE '

';