DB2 9.5 fixpack 1 stalls when running script

[WP]

Hello, I have a very simple script (or would you call it a batch
file?) with the following content:
connect to mydb2;
DROP TABLE staff_employee_ address;
DROP TABLE staff_employee_ address_telepho ne;
DROP TABLE staff_employee;
DROP TABLE staff;
commit;
terminate;

I'm trying to run this script using a combination of db2cmd / db2 clp
launched from a Java program. I'm using Windows Vista, so that's why I
am using db2cmd (that sets up the environment for the db2 clp if I
understood things correctly).

The actual java code is:
String cmd = "db2cmd -c -i -w db2 +c -tvf path/to/script.sql -z
outfile.txt"
Process p = Runtime.getRunt ime().exec(cmd) ;

System.out.prin tln("The exit code was: " p.waitFor());

Unfortunately, waitFor() never returns so my Java program grinds to a
halt. If I look in outfile.txt I see:
connect to mydb2

Database Connection Information

Database server = DB2/NT 9.5.1
SQL authorization ID = LOKALADM
Local database alias = MYDB2


DROP TABLE staff_employee_ address
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "LOKALADM.STAFF _EMPLOYEE_ADDRE SS" is an undefined name.
SQLSTATE=42704

DROP TABLE staff_employee_ address_telepho ne
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:

as you can see it has stopped writing output midway. This particular
run was done at a time where the tables that are supposed to be
dropped doesn't actually exist.

Here I'm running it when the tables do exist, waitFor() still doesn't
return and the outfile lacks information about the commit and
terminate commands being run (the tables are dropped, however):
connect to mydb2

Database Connection Information

Database server = DB2/NT 9.5.1
SQL authorization ID = LOKALADM
Local database alias = MYDB2


DROP TABLE staff_employee_ address
DB20000I The SQL command completed successfully.

DROP TABLE staff_employee_ address_telepho ne
DB20000I The SQL command completed successfully.

DROP TABLE staff_employee
DB20000I The SQL command completed successfully.

DROP TABLE staff
DB20000I The SQL command completed successfully.

So then I tried issuing the four drop table statements from inside the
control center and it only stalls if my java program is currently
running and stalled.

Any ideas on how to solve it? My boss is getting a bit impatient I
think, heh, because this was supposed to be a straightforward task. I
was actually using db2batch at first but ran into problems as soon as
I used this script and tried to drop non-existant tables so I switched
to db2cmd/db2 clp instead but still no go, I'm afraid. :-(

The commit; terminate; part was added just now to see if that solved
things but, unfornately, it didn't.

- Eric Lilja

[Dave Hughes]

Re: DB2 9.5 fixpack 1 stalls when running script

WP wrote:

Hello, I have a very simple script (or would you call it a batch
file?) with the following content:
connect to mydb2;
DROP TABLE staff_employee_ address;
DROP TABLE staff_employee_ address_telepho ne;
DROP TABLE staff_employee;
DROP TABLE staff;
commit;
terminate;
>
I'm trying to run this script using a combination of db2cmd / db2 clp
launched from a Java program. I'm using Windows Vista, so that's why I
am using db2cmd (that sets up the environment for the db2 clp if I
understood things correctly).
>
The actual java code is:
String cmd = "db2cmd -c -i -w db2 +c -tvf path/to/script.sql -z
outfile.txt"
Process p = Runtime.getRunt ime().exec(cmd) ;
>
System.out.prin tln("The exit code was: " p.waitFor());
>
Unfortunately, waitFor() never returns so my Java program grinds to a
halt.

I'm unfamiliar with this sort of thing in Java - does the above do
anything like redirect stdin/stdout/stderr to pipes? If so, bear in
mind you'll need to read from stdout/stderr to stop the buffers
becoming full and stalling the process (you may also need to do various
things like closing the write end of the stdin pipe - depends on the
framework).

As for why the script stalls in the control center only when your
script is also stalled - that's probably because your script is running
without auto-commit so while it's stalled it's holding write locks on
the system catalog tables for the uncommitted drops, hence the control
center script is waiting for that transaction to commit or rollback.


Cheers,

Dave.

[WP]

Re: DB2 9.5 fixpack 1 stalls when running script

I think I may have found a workaround but it's not perfect.

On 4 Juni, 13:22, WP <mindcoo...@gma il.comwrote:

Hello, I have a very simple script (or would you call it a batch
file?) with the following content:
connect to mydb2;
DROP TABLE staff_employee_ address;
DROP TABLE staff_employee_ address_telepho ne;
DROP TABLE staff_employee;
DROP TABLE staff;
commit;
terminate;
>
I'm trying to run this script using a combination of db2cmd / db2 clp
launched from a Java program. I'm using Windows Vista, so that's why I
am using db2cmd (that sets up the environment for the db2 clp if I
understood things correctly).
>
The actual java code is:
String cmd = "db2cmd -c -i -w db2 +c -tvf path/to/script.sql -z
outfile.txt"

I dropped the -i and -w flags for db2cmd and turned on autocommit by
removing +c from db2 (clp). Each script ends with connect reset; and
terminate;. Now my result files get all output and all changes seem to
make it through to db2 and being commited. The not so perfect thing
about it is the two console windows I can see temporarily on my
screen. But I will settle for that for now and continue working on
other parts of the code. Replies still welcome, however.
[snip rest of my op]

- Eric

[Dave Hughes]

Re: DB2 9.5 fixpack 1 stalls when running script

WP wrote:

I think I may have found a workaround but it's not perfect.
>
On 4 Juni, 13:22, WP <mindcoo...@gma il.comwrote:

Hello, I have a very simple script (or would you call it a batch
file?) with the following content:
connect to mydb2;
DROP TABLE staff_employee_ address;
DROP TABLE staff_employee_ address_telepho ne;
DROP TABLE staff_employee;
DROP TABLE staff;
commit;
terminate;

I'm trying to run this script using a combination of db2cmd / db2
clp launched from a Java program. I'm using Windows Vista, so
that's why I am using db2cmd (that sets up the environment for the
db2 clp if I understood things correctly).

The actual java code is:
String cmd = "db2cmd -c -i -w db2 +c -tvf path/to/script.sql -z
outfile.txt"

>
I dropped the -i and -w flags for db2cmd and turned on autocommit by
removing +c from db2 (clp). Each script ends with connect reset; and
terminate;. Now my result files get all output and all changes seem to
make it through to db2 and being commited. The not so perfect thing
about it is the two console windows I can see temporarily on my
screen. But I will settle for that for now and continue working on
other parts of the code. Replies still welcome, however.
[snip rest of my op]

I think that confirms my original suspicions. You could add the +c back
in and it should still work; the window created by db2cmd (without -i
and -w) provides stdout/stderr handles which soak up any output
automatically, preventing the process from stalling. So, that's one
solution.

If you want it to work without db2cmd creating any windows, you need to
add -i and -w back in, and figure out how to get the Java process to
periodically read the output to stop the buffers filling up. I'm not
that familiar with Java, but I've had a quick look at the reference at
http://java.sun.com/j2se/1.3/docs/api/. According to the Process class'
reference (which is what Runtime.exec() returns):

"The created subprocess does not have its own terminal or console. All
its standard io (i.e. stdin, stdout, stderr) operations will be
redirected to the parent process through three streams
(Process.getOut putStream(), Process.getInpu tStream(),
Process.getErro rStream()). The parent process uses these streams to
feed input to and get output from the subprocess. Because some native
platforms only provide limited buffer size for standard input and
output streams, failure to promptly write the input stream or read the
output stream of the subprocess may cause the subprocess to block, and
even deadlock."

Which is what I suspect is happening. In other words, this isn't a DB2
specific thing - it's just what you need to do when dealing with
console processes that produce lots of output. I also found the
following thread on the Java forums:

http://forum.java.sun.com/thread.jspa?forumID=256&threadID=484376

It includes some code for setting up stream handlers to redirect the
standard handles of the parent to/from the child, and background
threads to handle all the reading & writing.


Cheers,

Dave.