How do we execute this command through java?

[technocrat]

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

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

[Knut Stolze]

Re: How do we execute this command through java?

technocrat wrote:
[color=blue]
> db2 -t -v -f/home.../filename >output_file-name
>
> I have a java stored procedure..whic h has to run the above
> command...not sure how i can run this command through java..
> any suggestions are appreciated..[/color]

System.getRunti me().exec("..." );

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[technocrat]

Re: How do we execute this command through java?

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

[Knut Stolze]

Re: How do we execute this command through java?

technocrat wrote:
[color=blue]
> Doesnt seem like getRuntime method call is present in System
> class...gives me an error....any help??[/color]

Right, my mistake. Then use the Runtime class.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[technocrat]

Re: How do we execute this command through java?

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

[technocrat]

Re: How do we execute this command through java?

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

BufferedReader input = new BufferedReader
(new InputStreamRead er(p.getInputSt ream()));
while ((line = input.readLine( )) != null) {
System.out.prin tln(line);
}
input.close();
System.out.prin tln("Output Print done");


When I run this...I get an error saying...db2cmd not found...
SQL4302N Procedure or user-defined function "CONTROL.INITTI ERONE",
specific
name "SQL06042413311 4600" aborted with an exception "db2cmd: not
found".
SQLSTATE=38501
What might be going wrong???

[tuarek]

Re: How do we execute this command through java?

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

Please refer to:

http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=/com.ibm.itpc_fabric.doc/btacpi23104.htm

regards,

tuarek

[Dave Hughes]

Re: How do we execute this command through java?

tuarek wrote:
[color=blue]
> 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_fa bric.doc/btacpi23104.htm[/color]

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]

Re: How do we execute this command through java?

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]

Re: How do we execute this command through java?

tuarek wrote:
[color=blue]
> 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.[/color]

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 :-)
[color=blue]
> 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.[/color]

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


Dave.
--

[Gregor Kovaè]

Re: How do we execute this command through java?

technocrat wrote:
[color=blue]
> String line;
> Process p = Runtime.getRunt ime().exec("db2 cmd /c /w /i db2 -tvf
> /home/filename.txt >output");
>
> BufferedReader input = new BufferedReader
> (new InputStreamRead er(p.getInputSt ream()));
> while ((line = input.readLine( )) != null) {
> System.out.prin tln(line);
> }
> input.close();
> System.out.prin tln("Output Print done");
>
>
> When I run this...I get an error saying...db2cmd not found...
> SQL4302N Procedure or user-defined function "CONTROL.INITTI ERONE",
> specific
> name "SQL06042413311 4600" aborted with an exception "db2cmd: not
> found".
> SQLSTATE=38501
> What might be going wrong???[/color]
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.getRunt ime().exec("/bin/sh -c db2cmd -c -w -i
db2 -t -f /home/filename.txt >output");

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

[technocrat]

Re: How do we execute this command through java?

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.InitTie rOne
*/
package PKG604240121128 28;

import java.io.Buffere dReader;
import java.io.InputSt reamReader;
import java.sql.*; // JDBC classes

public class InitTierOne
{
public static void initTierOne () throws SQLException, Exception
{
// Get connection to the database
Connection con =
DriverManager.g etConnection("j dbc:default:con nection");
PreparedStateme nt stmt = null;
boolean bFlag;
String sql;
String line;
Process p = Runtime.getRunt ime().exec("db2 profile /c /w /i db2
-tvf ecd2061.txt >ecd2061");

BufferedReader input = new BufferedReader
(new InputStreamRead er(p.getInputSt ream()));
while ((line = input.readLine( )) != null) {
System.out.prin tln(line);
}
input.close();
System.out.prin tln("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]

Re: How do we execute this command through java?

technocrat wrote:
[color=blue]
> 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...[/color]

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]

Re: How do we execute this command through java?

technocrat wrote:
[color=blue]
> 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.InitTie rOne
> */
> package PKG604240121128 28;
>
> import java.io.Buffere dReader;
> import java.io.InputSt reamReader;
> import java.sql.*; // JDBC classes
>
> public class InitTierOne
> {
> public static void initTierOne () throws SQLException, Exception
> {
> // Get connection to the database
> Connection con =
> DriverManager.g etConnection("j dbc:default:con nection");
> PreparedStateme nt stmt = null;
> boolean bFlag;
> String sql;
> String line;
> Process p = Runtime.getRunt ime().exec("db2 profile /c /w /i db2
> -tvf ecd2061.txt >ecd2061");
>
> BufferedReader input = new BufferedReader
> (new InputStreamRead er(p.getInputSt ream()));
> while ((line = input.readLine( )) != null) {
> System.out.prin tln(line);
> }
> input.close();
> System.out.prin tln("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[/color]

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]

Re: How do we execute this command through java?

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)