How do we execute this command through java?

[technocrat]

db2 -t -v -f/home.../filename >output_file-name

I have a java stored procedure..which has to run the above
command...not sure how i can run this command through java..
any suggestions are appreciated..

[Knut Stolze]

technocrat wrote:

db2 -t -v -f/home.../filename >output_file-name

I have a java stored procedure..which has to run the above
command...not sure how i can run this command through java..
any suggestions are appreciated..

System.getRuntime().exec("...");

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[technocrat]

Doesnt seem like getRuntime method call is present in System
class...gives me an error....any help??

[Knut Stolze]

technocrat wrote:

Doesnt seem like getRuntime method call is present in System
class...gives me an error....any help??

Right, my mistake. Then use the Runtime class.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[technocrat]

Thanks Knut....it was in runtime class not system...but a little
googling on the runtime.getruntime..solved my problem...Thanks a ton
once again!

[technocrat]

String line;
Process p = Runtime.getRuntime().exec("db2cmd /c /w /i db2 -tvf
/home/filename.txt >output");

BufferedReader input = new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
System.out.println("Output Print done");
When I run this...I get an error saying...db2cmd not found...
SQL4302N Procedure or user-defined function "CONTROL.INITTIERONE",
specific
name "SQL060424133114600" aborted with an exception "db2cmd: not
found".
SQLSTATE=38501
What might be going wrong???

[tuarek]

db2cmd is for Microsoft Windows systems. You have to use db2profile for
linux & unix systems..

Please refer to:

http://publib.boulder.ibm.com/infoce...tacpi23104.htm

regards,

tuarek

[Dave Hughes]

tuarek wrote:

db2cmd is for Microsoft Windows systems. You have to use db2profile
for linux & unix systems..

Please refer to:

http://publib.boulder.ibm.com/infoce...ndex.jsp?topic
=/com.ibm.itpc_fabric.doc/btacpi23104.htm

Indeed, db2cmd is only used on Windows systems. However, you can't
"just" run db2profile on a Linux/UNIX system to the same effect:
db2profile is a shell script sets up certain environment variables for
the DB2 CLP in the calling shell (DB2DIR, INSTHOME, PATH, CLASSPATH,
VWSPATH, LD_LIBRARY_PATH, LIBPATH, and DB2INSTANCE).

Java can't run a shell script directly: you'd need to start a shell and
run the script within that. However, you'd then need to start the DB2
CLP within that *same* shell (otherwise you'll lose the changes to the
environment).

Alternatively, I assume Java provides some mechanism of tweaking the
environment of a child process when executed, in which case you could
avoid db2profile altogether by emulating its actions when executing the
CLP (launching the CLP with an appropriately tweaked environment).

However, in your OP you mention that this is all part of a stored
procedure. In other words, you're trying to execute an SQL script with
the DB2 CLP from *within* a Java stored procedure in a DB2 database?
That sounds a bit odd to me... What exactly are you trying to
accomplish here? It might be there's a simpler way of doing it with an
SQL stored procedure.
Dave.

--

[tuarek]

Dave,

I haven't tried yet but don't you think that wouldn't "db2profile &&
db2 -tvf ..." work? assuming no problem with java program...

I believe "&&" will run them sequentially in the same shell call. I'll
try.

BTW: Running a db2 script from SP didn't make sense to me as well, but
then I thought technocrat may be trying to dynamically generate this
script with some external interactions.

regards,

taurek

[Dave Hughes]

tuarek wrote:

Dave,

I haven't tried yet but don't you think that wouldn't "db2profile &&
db2 -tvf ..." work? assuming no problem with java program...

I believe "&&" will run them sequentially in the same shell call.
I'll try.
It will do assuming the Runtime.exec method actually spawns a shell
instead of a simple child process (like fork/exec under UNIX, or
CreateProcess under Windows). If it doesn't I guess one could use
something like "/bin/sh -c db2profile && db2 -tvf ..." instead (as you
can probably guess I'm not a Java coder :-)
BTW: Running a db2 script from SP didn't make sense to me as well, but
then I thought technocrat may be trying to dynamically generate this
script with some external interactions.

Could well be; it's certainly one situation where such a hack is
neeeded. Guess we'll see...
Dave.
--

[Gregor Kovaè]

technocrat wrote:

String line;
Process p = Runtime.getRuntime().exec("db2cmd /c /w /i db2 -tvf
/home/filename.txt >output");

BufferedReader input = new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
System.out.println("Output Print done");
When I run this...I get an error saying...db2cmd not found...
SQL4302N Procedure or user-defined function "CONTROL.INITTIERONE",
specific
name "SQL060424133114600" aborted with an exception "db2cmd: not
found".
SQLSTATE=38501
What might be going wrong???

I have it working this way:
I've put
if [ -f /home/db2inst1/sqllib/db2profile ]; then
. /home/db2inst1/sqllib/db2profile
fi

into my .bash_profile.
Then run your program with:
Process p = Runtime.getRuntime().exec("/bin/sh -c db2cmd -c -w -i
db2 -t -f /home/filename.txt >output");

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[technocrat]

Well Dave the thing I was planning to do was
I have a txt file which has all the insert commands to different tables
in a database. I can hardcode those insert statements as a simple sql
insert stattements using jdbc but there are almost 30-40 of them and
might change in future...so I was thinking of writing a JAVA stored
procedure which would call the "db2 -tvf/home./...filename" command to
read from that filename and populate the database...

This is the code...but if there is a better way to do the above
problem, kindly let me know
/**
* JDBC Stored Procedure CONTROL.InitTierOne
*/
package PKG60424012112828;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*; // JDBC classes

public class InitTierOne
{
public static void initTierOne () throws SQLException, Exception
{
// Get connection to the database
Connection con =
DriverManager.getConnection("jdbc:default:connecti on");
PreparedStatement stmt = null;
boolean bFlag;
String sql;
String line;
Process p = Runtime.getRuntime().exec("db2profile /c /w /i db2
-tvf ecd2061.txt >ecd2061");

BufferedReader input = new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
System.out.println("Output Print done");

}
}

And the text file from wehich it reads is as below

connect to DB
insert into table tablename (values);
commit;
connect reset;
quit

[Knut Stolze]

technocrat wrote:

Well Dave the thing I was planning to do was
I have a txt file which has all the insert commands to different tables
in a database. I can hardcode those insert statements as a simple sql
insert stattements using jdbc but there are almost 30-40 of them and
might change in future...so I was thinking of writing a JAVA stored
procedure which would call the "db2 -tvf/home./...filename" command to
read from that filename and populate the database...

In this case, I'd recommend that you write some (Java) code that reads the
file and executes the statements therein directly through JDBC. The thing
is that the spawning of another process inside an external routine is a not
supported configuration.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Dave Hughes]

technocrat wrote:

Well Dave the thing I was planning to do was
I have a txt file which has all the insert commands to different
tables in a database. I can hardcode those insert statements as a
simple sql insert stattements using jdbc but there are almost 30-40
of them and might change in future...so I was thinking of writing a
JAVA stored procedure which would call the "db2
-tvf/home./...filename" command to read from that filename and
populate the database...

This is the code...but if there is a better way to do the above
problem, kindly let me know
/**
* JDBC Stored Procedure CONTROL.InitTierOne
*/
package PKG60424012112828;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*; // JDBC classes

public class InitTierOne
{
public static void initTierOne () throws SQLException, Exception
{
// Get connection to the database
Connection con =
DriverManager.getConnection("jdbc:default:connecti on");
PreparedStatement stmt = null;
boolean bFlag;
String sql;
String line;
Process p = Runtime.getRuntime().exec("db2profile /c /w /i db2
-tvf ecd2061.txt >ecd2061");

BufferedReader input = new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
System.out.println("Output Print done");

}
}

And the text file from wehich it reads is as below

connect to DB
insert into table tablename (values);
commit;
connect reset;
quit

If all you're doing is inserting data into a variety of different
tables I'd suggest dispensing with SQL in the text file altogether:
store the data and the target table in some format (whatever you like,
be it CSV, XML, or whatever) and have your external routine read the
data file and run the necessary INSERT statements (saves all that
messing around with spawning external processes and the associated
portability problems).

One thing that still confuses me ... why does this have to be in an
external routine / stored procedure?

I'm guessing you want the SQL script to sit on the server (for security
reasons or some such?) and have the ability to execute a simple CALL
statement from a client, causing the server to execute the script. I'm
just wondering why using the CLP via SSH or something similar isn't
acceptable (assuming your server is UNIX/Linux)?

For example:

$ ssh dave@myserver "db2 -tvf myscript.sql"

seems just as easy (if not easier) than:

$ db2 CONNECT TO MYDB USER dave USING mypassword
$ db2 CALL MYPROC
$ db2 CONNECT RESET
Dave.
--

[technocrat]

It really doesnt need to be in a stored procedure, the only reason I
have it there..is that I have a streod procedure to init the tier 3
tables (which I wrote earlier) so to init the tier one tables now I
started by default as a stor ed procedure thinking if i can execute
just db2 -tvf/filename command from some srt of a technique the problem
would be solved, but seems to be much more difficult than that. I like
the technique you mentioned...but I would rather do the inserts
manually by calling the command from CLP (earlier I was thinking of
automating this process which lead to all this mess)