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**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[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.getRunt ime().exec(comm and);
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_C MD 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|rollbac k>