"Gregor Kovac" <gregor.kovac (AT) mikropis (DOT) si> wrote

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

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

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

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 ?

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

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

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

Kovi wrote:

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.

--

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

Kovi wrote:

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.

--