Running SQL script

[Gregor Kovaè]

Hi!

I have an SQL script with bunch of CREATE TABLES, ALTER TABLES, ...
I'd like it to run transactially. I have found that id I run a ALTER
STATEMENT inside a transaction and then roll it back the changes disapear.
So if this works for one statement it should also for a script, right ?
I run the script with the following command:
db2cmd -c -w -i db2 -t -f SCRIPT.SQL
In this SCRIPT.SQL the connection to the database is made (CONNECT TO DB
USER ....).

What do I have to do to run this inside a transaction?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Mark A]

"Gregor Kovac" <gr**********@mikropis.si> wrote in message
news:Hc********************@news.siol.net...

Hi!

I have an SQL script with bunch of CREATE TABLES, ALTER TABLES, ...
I'd like it to run transactially. I have found that id I run a ALTER
STATEMENT inside a transaction and then roll it back the changes disapear.
So if this works for one statement it should also for a script, right ?
I run the script with the following command:
db2cmd -c -w -i db2 -t -f SCRIPT.SQL
In this SCRIPT.SQL the connection to the database is made (CONNECT TO DB
USER ....).

What do I have to do to run this inside a transaction?

Best regards,
Kovi

Try using +c instead of -c, and make sure you put a commit at the end.

[Artur]

First of all you have to switch off AUTOCOMMIT option.

You can do this by passing +c option to db2 interpreter:

db2cmd -c -w -i db2 +c -t -f SCRIPT.SQL

but you can also control autocommit within your script (which I
prefer):

SCRIPT.SQL:
-----------------
connect to yourdb;
update command options using c off;
....
commit;

-- Artur Wronski

[rAinDeEr]

Hi Arthur,

Can u explain the how the auto commit works during a
transaction ?
Suppose i have the statement db2 +c insert into mytable......
how does it work ? I am not getting the clear picture.....

regards,
RaInDeeR

[Kovi]

Yes, I did come to that conclusion also, but...
If one statement fails there should be a rollback in there instead of
commit. If there is commit at the end and some statements fail and some
succeed then I have a problem.

Any comments ?

[Shashi Mannepalli]

This is how i do in UNIX ...mostly same thing in WINDOWS

db2 +c -stvf <file> and in the FILE no COMMIT/ROLLBACK's.

So if the statement fails somewhere it will stop execution and comes
out.

Then i do a db2 "rollback" then all the changes are gone. Now i will
fix the file now and rerun again.

cheers...
Shashi Mannepalli

[Kovi]

OK. I agree. This is going to work if you run it from shell, but how if
you try to run it from a Java program for example.
There do you a:
String command = "cmd /c db2cmd -c -w -i db2 +c -td# -f SCRIPT.SQL";
Process proc = Runtime.getRuntime().exec(command);
int errorCode = proc.waitFor();

I can see that something was wrong or not (by the value in errorCode).
How do I do a commit or rollback ?

Regards,
Kovi

[Artur]

RaInDeeR,

If autocommit is ON (-c):
- each successful statement or command is automatically committed,
- each failed statement or command is automatically rolled back.
When autocommit is OFF (+c):
- COMMIT or ROLLBACK must be issued explicitly.
If you have following SQL script which is run with autocommit OFF, and
table definition: create table a (c1 int)

[1] insert into a(c1) values (99);
[2] insert into a(c2) values (10);
[3] commit;

then

[1] is successful
[2] fails because of wrong column name
[3] commits all changes, which means: value 99 is in the table.

If you would like to group the statements and have [1] and [2] all
successful or all failed you can use compound SQL:

[1] begin atomic
insert into a(c1) values (99);
insert into a(c2) values (10);
end @
[2] commit @

Which means that the two inserts are treated like one SQL instruction.
None is inserted into table a (there are some restrictions to compound
SQL, eg. DDL).

You can also use option 's' which will stop processing of SQL script
when first error encounters.

For this script below:
[1] update command options using c off;
[2] update command options using s on;
[3] insert into a(c1) values (99);
[4] insert into a(c2) values (88);
[5] commit;

this means stopping after instruction [4]. Then you can manually
rollback or commit the insert [3]. If you are running the script from
seperate process that terminates at the end
(eg. db2cmd -c -w -i db2 -s +c -td# -f SCRIPT.SQL) all the changes are
rolled back (as the connection terminates before issuing commit or
rollback).

My suggestion: if you are looking for more control (here: error
handling) use stored procedures.

-- Artur Wronski

[Dave Hughes]

Kovi wrote:

OK. I agree. This is going to work if you run it from shell, but how
if you try to run it from a Java program for example.
There do you a:
String command = "cmd /c db2cmd -c -w -i db2 +c -td# -f SCRIPT.SQL";
Process proc = Runtime.getRuntime().exec(command);
int errorCode = proc.waitFor();

I can see that something was wrong or not (by the value in errorCode).
How do I do a commit or rollback ?

Unfortunately, by that point the db2cmd process will have gone, closing
the backend process, and closing the connection to the database. I
can't recall if the default action is to commit or rollback the active
transaction at that point, but either way, there's nothing you can do.

If you want to explicitly control what happens at the end depending on
the outcome of the statements in the script you've got a couple of
options:

Option 1 : Don't use the CLP

Open a connection to the target database with JDBC and execute SQL
statements through that connection. I know very little Java, but if its
anything like other languages then there shouldn't be any autocommit
when doing things this way - you have to commit and rollback explicitly
yourself. I'm not sure how failed statements would get reported in such
a situation (might be by return code, but I suspect it'll be by an
exception being raised).

The only disadvantage to this is that you won't get access to the CLP
commands this way, although you can use the SYSPROC.ADMIN_CMD procedure
in the later v8 fixpaks to access *some* CLP commands via SQL (e.g.
EXPORT, RUNSTATS, REORG and some others I can't remember).

Option 2 : Control the CLP interactively

This one is a *lot* more difficult, but will give you access to the
full range of CLP commands. Instead of passing the CLP a script file to
execute, you start the CLP process attaching pipes to its stdin and
stdout/stderr streams.

You then feed one command at a time through the stdin stream, and watch
the stdout stream for output, parsing it to determine if an error
occurred (this is more difficult than it sounds). You can't use error
codes because its an ongoing process.

Basically, you're making a program which'll act like a user typing
things into an interactive CLP session.

It's a horrible way of doing things and there's very few reasons one
would actually *need* to do this. I've only needed it once and that was
for a very esoteric reason :-)
HTH,

Dave.

--

[Kovi]

Hi!

Thanks for all your help.
I also susspect I'd have to do something in the line of Option 2, but
that was not very attractive to me. I thought that someone knows a
secret. :)

Best regards,
Kovi

[Dave Hughes]

Kovi wrote:

Hi!

Thanks for all your help.
I also susspect I'd have to do something in the line of Option 2, but
that was not very attractive to me. I thought that someone knows a
secret. :)

Hmm ... Could you tell us what your script is actually trying to do? My
hunch is that there's a better way to do whatever you need to do than
messing around with pipes, sub-processes, and output parsing.

The esoteric reason I mentioned previously was that I wished to create
a "nicer" GUI front-end for the CLP than the standard DB2 Command
Center. That's a pretty rare situation, and while it worked, more or
less, it was still pretty buggy (never got around to finishing it
properly). I've never found any other reason to go this route.

Furthermore, while the first method (connect with JDBC, run SQL
statements) doesn't give you access to the full range of CLP commands,
if you're concerned about the ability to commit / rollback then that
shouldn't really bother you (CLP commands aren't generally under
transaction control; you can't rollback a CREATE DATABASE command, nor
an IMPORT command, nor CATALOG, etc., etc.)
Dave.

--

[Ian]

Dave Hughes wrote:

Kovi wrote:

OK. I agree. This is going to work if you run it from shell, but how
if you try to run it from a Java program for example.
There do you a:
String command = "cmd /c db2cmd -c -w -i db2 +c -td# -f SCRIPT.SQL";
Process proc = Runtime.getRuntime().exec(command);
int errorCode = proc.waitFor();

I can see that something was wrong or not (by the value in errorCode).
How do I do a commit or rollback ?

Unfortunately, by that point the db2cmd process will have gone, closing
the backend process, and closing the connection to the database. I
can't recall if the default action is to commit or rollback the active
transaction at that point, but either way, there's nothing you can do.

If you want to explicitly control what happens at the end depending on
the outcome of the statements in the script you've got a couple of
options:

Option 1 : Don't use the CLP

Open a connection to the target database with JDBC and execute SQL
statements through that connection. I know very little Java, but if its
anything like other languages then there shouldn't be any autocommit
when doing things this way - you have to commit and rollback explicitly
yourself. I'm not sure how failed statements would get reported in such
a situation (might be by return code, but I suspect it'll be by an
exception being raised).

The only disadvantage to this is that you won't get access to the CLP
commands this way, although you can use the SYSPROC.ADMIN_CMD procedure
in the later v8 fixpaks to access *some* CLP commands via SQL (e.g.
EXPORT, RUNSTATS, REORG and some others I can't remember).

Option 2 : Control the CLP interactively

This one is a *lot* more difficult, but will give you access to the
full range of CLP commands. Instead of passing the CLP a script file to
execute, you start the CLP process attaching pipes to its stdin and
stdout/stderr streams.

You then feed one command at a time through the stdin stream, and watch
the stdout stream for output, parsing it to determine if an error
occurred (this is more difficult than it sounds). You can't use error
codes because its an ongoing process.

Basically, you're making a program which'll act like a user typing
things into an interactive CLP session.

It's a horrible way of doing things and there's very few reasons one
would actually *need* to do this. I've only needed it once and that was
for a very esoteric reason :-)

Option 3: Don't run scripts with 'db2cmd -c -w -i db2 ...".

Open DB2 Command Window, which gives you a CMD.exe shell, with the DB2
environment initialized. Then you can do things like:

db2 +c -stvf file.ddl
<review output>
db2 <commit|rollback>