473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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_telephone;
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.getRuntime().exec(cmd);

System.out.println("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_ADDRESS" is an undefined name.
SQLSTATE=42704

DROP TABLE staff_employee_address_telephone
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_telephone
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
Jun 27 '08 #1
3 3675
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_telephone;
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.getRuntime().exec(cmd);

System.out.println("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.
Jun 27 '08 #2
WP
I think I may have found a workaround but it's not perfect.

On 4 Juni, 13:22, WP <mindcoo...@gmail.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_telephone;
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
Jun 27 '08 #3
WP wrote:
I think I may have found a workaround but it's not perfect.

On 4 Juni, 13:22, WP <mindcoo...@gmail.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_telephone;
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.getOutputStream(), Process.getInputStream(),
Process.getErrorStream()). 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.jsp...hreadID=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.
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: alederer | last post by:
Hallo! I have the following situation: DB2 V6.1 OS/390 and DB2 V6.1 auf AIX 4.3.3 running the database servers. A windows NT machine with db2 V7.1.040 FP3 (WR21254) client. Now I have a JDBC...
6
by: cat | last post by:
Hi Folks, We are going to start a grand new project, which is DB2v7 FP10a sitting on AIX4.3.3 on M80 server. Here is my question, 1) According to Fixpack installation guide, I have to install...
1
by: John Wheeler | last post by:
Hi, We upgraded the Windows ADC V8.1 to fixpack 5 on some Windows 2000 (Dutch Version) professional machines and something strange happened. When we select decimal columns in our queries the...
4
by: pshindle | last post by:
DB2 Team - I just downloaded and unzipped the new Fixpack 9 for DB2 ESE V8 for Windows (FP9_WR21350_ESE.exe). I then burned the unzipped Fixpack files to a CD. I proceded to install this...
4
by: Mark A | last post by:
Trying to upgrade alternate FP9 to alternate FP9a on Red Hat Linux. After running the following command: ../installAltFixPak -y it says it was successful, but no new directory is created in...
8
by: Jothishankar | last post by:
Hi, I am new to c#. I am trying to build an application that does backup of files to an external hard disk. My application behaves strangely. When i run the application under debug mode (F5),...
5
by: jbenner | last post by:
I have opened a PMR for this with IBM, and am not asking for advice from the DB2 DBA community. I am posting this as an FYI that DB2 Health Monitor, even at the latest version of DB2, still can cause...
6
by: Mark A | last post by:
In version 8.2 ESE we used alternate fixpacks to allow multiple fixpack levels for different instances, and to allow an instance to be deprecated back to the previous fixpack (with certain...
3
by: cwinay | last post by:
Hey guys, I couldn't find a dedicated hardware section here so I picked this section to ask my question assuming that system administrators of all kind hit this forum. Guide me to an appropriate...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.