Java + DB2, import table data

[WP]

Hello, I've just started to work with DB2 (using the express edition
version 9.5 under windows vista) and I'm accessing it through a java
program (my java's very rusty). The java program loads the driver,
connects and tries to create two tables. That works just fine. Then it
tries to import data into one of the tables but it seems I can't use
an import command with Statement.execute() because an exception is
thrown if I attempt to do this.
Is this possible and, if so, how?

Here's the acutal call that triggers the exception:
statement.execute("IMPORT FROM 'C:/staff.xml.xsd' OF DEL METHOD P(1)
MESSAGES 'C:/messages.txt' INSERT INTO STAFF_AS_XML
(STAFF_AS_XML_COL);");

One of the tables has a single xml column and I have an xml-file I
want to import to that table from the java program. The command works
under control center (but not always, which is weird, but that is a
separate issue).

Thanks for reading and thanks for any replies.

[Dave Hughes]

WP wrote:

Hello, I've just started to work with DB2 (using the express edition
version 9.5 under windows vista) and I'm accessing it through a java
program (my java's very rusty). The java program loads the driver,
connects and tries to create two tables. That works just fine. Then it
tries to import data into one of the tables but it seems I can't use
an import command with Statement.execute() because an exception is
thrown if I attempt to do this.
Is this possible and, if so, how?

Here's the acutal call that triggers the exception:
statement.execute("IMPORT FROM 'C:/staff.xml.xsd' OF DEL METHOD P(1)
MESSAGES 'C:/messages.txt' INSERT INTO STAFF_AS_XML
(STAFF_AS_XML_COL);");

One of the tables has a single xml column and I have an xml-file I
want to import to that table from the java program. The command works
under control center (but not always, which is weird, but that is a
separate issue).

Thanks for reading and thanks for any replies.

The IMPORT command isn't SQL; it's a "CLP command" (which is why it
doesn't appear under the SQL reference in the InfoCenter, but in a
separate section along with other commands like CREATE DATABASE, LOAD,
etc.)

Before DB2 9, you could only execute CLP commands from the CLP (the db2
command line executable), with the exception of one or two commands.
Starting with DB2 9, you can execute many (but not all) CLP commands
via the ADMIN_CMD() [1] stored procedure, including IMPORT [2], EXPORT
[3] and LOAD [4].

However, one important thing to be aware of with ADMIN_CMD(). The
procedure runs the command *on the server*. Hence, for IMPORT, the
input data file must reside on the server (see [2]).

There's probably some "proper" JDBC method for calling stored
procedures, or I suspect you could just do:

statement.execute("CALL SYSPROC.ADMIN_CMD('IMPORT FROM
''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
INSERT INTO STAFF_AS_XML (STAFF_AS_XML_COL)')");
[1]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.rtn.doc/doc/r0012547.html

[2]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023575.html

[3]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023573.html

[4]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023577.html
Cheers,

Dave.

[WP]

On 11 Apr, 15:51, "Dave Hughes" <[e-mail]:
[snip]

>
The IMPORT command isn't SQL; it's a "CLP command" (which is why it
doesn't appear under the SQL reference in the InfoCenter, but in a
separate section along with other commands like CREATE DATABASE, LOAD,
etc.)

Before DB2 9, you could only execute CLP commands from the CLP (the db2
command line executable), with the exception of one or two commands.
Starting with DB2 9, you can execute many (but not all) CLP commands
via the ADMIN_CMD() [1] stored procedure, including IMPORT [2], EXPORT
[3] and LOAD [4].

However, one important thing to be aware of with ADMIN_CMD(). The
procedure runs the command *on the server*. Hence, for IMPORT, the
input data file must reside on the server (see [2]).

There's probably some "proper" JDBC method for calling stored
procedures, or I suspect you could just do:

statement.execute("CALL SYSPROC.ADMIN_CMD('IMPORT FROM
''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
INSERT INTO STAFF_AS_XML (STAFF_AS_XML_COL)')");

Hello Dave and thanks very much indeed for your helpful reply! Using
your suggestions I did this:
private void performImport(Connection db2_con) throws SQLException {
CallableStatement stmt = db2_con.prepareCall("CALL
SYSPROC.ADMIN_CMD(?)");
stmt.setString(1, "IMPORT FROM \"C:/staff.xml.xsd\" OF DEL METHOD P
(1) INSERT INTO STAFF_AS_XML (STAFF_AS_XML_COL)");

stmt.executeQuery();
}

which seems to work just fine. I used Control Center to "perform" an
import and I used the commad string it uses (which it can show me).
However, I had to remove the MESSAGES-stuff or an exception is thrown.
I guess db2 doesn't want to communicate warnings and errors for the
import command when accessing it through jdbc->stored procedure which
is fine by me.
Also, this program is accessing a database running on the same host
and we don't expect this setup to change anytime soon.
Very many thanks again, Dave!

>
[1]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0012547.html

[2]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023575.html

[3]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023573.html

[4]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023577.html

Cheers,

Dave.

- WP