Java + DB2, import table data

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • WP
    #1

    Java + DB2, import table data

    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.execu te() 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.execu te("IMPORT FROM 'C:/staff.xml.xsd' OF DEL METHOD P(1)
    MESSAGES 'C:/messages.txt' INSERT INTO STAFF_AS_XML
    (STAFF_AS_XML_C OL);");

    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.
    Tags: None
    • Dave Hughes
      #2
      Re: Java + DB2, import table data

      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.execu te() 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.execu te("IMPORT FROM 'C:/staff.xml.xsd' OF DEL METHOD P(1)
      MESSAGES 'C:/messages.txt' INSERT INTO STAFF_AS_XML
      (STAFF_AS_XML_C OL);");
      >
      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.execu te("CALL SYSPROC.ADMIN_C MD('IMPORT FROM
      ''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
      INSERT INTO STAFF_AS_XML (STAFF_AS_XML_C OL)')");


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

        Comment

        • WP
          #3
          Re: Java + DB2, import table data

          On 11 Apr, 15:51, "Dave Hughes" <d...@waveform. plus.comwrote:
          [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.execu te("CALL SYSPROC.ADMIN_C MD('IMPORT FROM
          ''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
          INSERT INTO STAFF_AS_XML (STAFF_AS_XML_C OL)')");
          Hello Dave and thanks very much indeed for your helpful reply! Using
          your suggestions I did this:
          private void performImport(C onnection db2_con) throws SQLException {
          CallableStateme nt stmt = db2_con.prepare Call("CALL
          SYSPROC.ADMIN_C MD(?)");
          stmt.setString( 1, "IMPORT FROM \"C:/staff.xml.xsd\" OF DEL METHOD P
          (1) INSERT INTO STAFF_AS_XML (STAFF_AS_XML_C OL)");

          stmt.executeQue ry();
          }

          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

            Comment

            Previous template Next
            Working...