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 | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

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

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

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.
--

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)