Re: Running SQL script

#11

Dave Hughes wrote:

Quote:

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>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Running SQL script

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: Running SQL script - 05-23-2006 , 09:52 PM