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.
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
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
On Tuesday, February 13, 2018 at 9:02:56 AM UTC, Mark Barinstein wrote:
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
On Monday, February 19, 2018 at 3:06:03 PM UTC+3, nps.fe...@gmail.com wrote:
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
On Tuesday, February 13, 2018 at 9:02:56 AM UTC, Mark Barinstein wrote:
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
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@
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 57:24:02 |
Calls: | 6,652 |
Calls today: | 4 |
Files: | 12,200 |
Messages: | 5,331,023 |