DB2, Java, and multi-row inserts

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

    DB2, Java, and multi-row inserts

    We've just recently converted to DB2 V8 z/os for our backend database
    server (upgraded from DB2 V7).

    Our java applications run under WebSphere/Windows using the DB2 Type 4
    driver (V8 FP14).

    We were hoping to get our developers pointed in the right direction
    for performing multi-row inserts, but we're only finding COBOL
    examples. Like:

    <<Working storage>>
    01 host-variables.
    05 HV-ACT-NMBR-ARRAY PIC s9(04) USAGE COMP OCCURS 100
    TIMES.
    05 HV-ACT-CITY-ARRAY PIC x(40) OCCURS 100 TIMES.
    05 HV-ACT-NAME-ARRAY OCCURS 100 TIMES.
    49 HV-ACT-NMBR-ARRAY PIC s9(04) USGE COMP.
    49 HV-ACT-NAME-ARRAY PIC x(40).
    05 HV-IND-ARRAY PIC s9(04) USAGE COMP OCCURS 100
    TIMES.

    <<Procedure division>>
    EXEC SQL
    INSERT INTO test-table (col1, col2, col3)
    VALUES (:HV-ACT-NMBR-ARRAY,
    :HV-ACT-CITY-ARRAY,
    :HV-ACT-NAME-ARRAY )
    FOR 50 ROWS
    ATOMIC
    END-EXEC.

    Do you know if this possible to do with java and type 4 drivers?

    If so, are there any examples?

    Thanks,
    klh
    Tags: None
    • Mark A
      #2
      Re: DB2, Java, and multi-row inserts

      "klh" <khar0429@yahoo .comwrote in message
      news:eab82ae1-7c67-4daa-8ddf-c14e17259a08@d4 5g2000hsc.googl egroups.com...
      We've just recently converted to DB2 V8 z/os for our backend database
      server (upgraded from DB2 V7).
      >
      Our java applications run under WebSphere/Windows using the DB2 Type 4
      driver (V8 FP14).
      >
      We were hoping to get our developers pointed in the right direction
      for performing multi-row inserts, but we're only finding COBOL
      examples. Like:
      >
      <<Working storage>>
      01 host-variables.
      05 HV-ACT-NMBR-ARRAY PIC s9(04) USAGE COMP OCCURS 100
      TIMES.
      05 HV-ACT-CITY-ARRAY PIC x(40) OCCURS 100 TIMES.
      05 HV-ACT-NAME-ARRAY OCCURS 100 TIMES.
      49 HV-ACT-NMBR-ARRAY PIC s9(04) USGE COMP.
      49 HV-ACT-NAME-ARRAY PIC x(40).
      05 HV-IND-ARRAY PIC s9(04) USAGE COMP OCCURS 100
      TIMES.
      >
      <<Procedure division>>
      EXEC SQL
      INSERT INTO test-table (col1, col2, col3)
      VALUES (:HV-ACT-NMBR-ARRAY,
      :HV-ACT-CITY-ARRAY,
      :HV-ACT-NAME-ARRAY )
      FOR 50 ROWS
      ATOMIC
      END-EXEC.
      >
      Do you know if this possible to do with java and type 4 drivers?
      >
      If so, are there any examples?
      >
      Thanks,
      klh
      It is not the language you are using, it is the database server. The "FOR
      host-variable ROWS" syntax is available on DB2 for z/OS, but not in DB2 for
      Linux, UNIX, and Windows (unless it was added for 9.5).

      This example is from the DB2 LUW SQL Reference VOL 2 will insert 2 rows:

      INSERT INTO DEPARTMENT
      (DEPTNO, DEPTNAME, ADMRDEPT)
      VALUES
      ('B11', 'PURCHASING', 'B01'),
      ('E41', 'DATABASE ADMINISTRATION' , 'E01') ;

      Obviously, you can use parameter markers (?) with a prepared statement.

      If you want a variable number of rows, then you will have to build the
      statement in your code for the exact number of rows you want.


        Comment

        • Kalexin
          #3
          Re: DB2, Java, and multi-row inserts

          On Apr 8, 12:04 pm, klh <khar0...@yahoo .comwrote:
          We've just recently converted to DB2 V8 z/os for our backend database
          server (upgraded from DB2 V7).
          >
          Our java applications run under WebSphere/Windows using the DB2 Type 4
          driver (V8 FP14).
          >
          We were hoping to get our developers pointed in the right direction
          for performing multi-row inserts, but we're only finding COBOL
          examples. Like:
          >
          <<Working storage>>
          01 host-variables.
          05 HV-ACT-NMBR-ARRAY PIC s9(04) USAGE COMP OCCURS 100
          TIMES.
          05 HV-ACT-CITY-ARRAY PIC x(40) OCCURS 100 TIMES.
          05 HV-ACT-NAME-ARRAY OCCURS 100 TIMES.
          49 HV-ACT-NMBR-ARRAY PIC s9(04) USGE COMP.
          49 HV-ACT-NAME-ARRAY PIC x(40).
          05 HV-IND-ARRAY PIC s9(04) USAGE COMP OCCURS 100
          TIMES.
          >
          <<Procedure division>>
          EXEC SQL
          INSERT INTO test-table (col1, col2, col3)
          VALUES (:HV-ACT-NMBR-ARRAY,
          :HV-ACT-CITY-ARRAY,
          :HV-ACT-NAME-ARRAY )
          FOR 50 ROWS
          ATOMIC
          END-EXEC.
          >
          Do you know if this possible to do with java and type 4 drivers?
          >
          If so, are there any examples?
          >
          Thanks,
          klh
          JDBC supports batch update. Maybe this will be helpful
          http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.java/tjvbtupd.htm?resultof=%22%6d%75%6c%74%69%22%20%22%72%6f%77%22%20%22%69%6e%73%65%72%74%22%20%22%6a%61%76%61%22%20

            Comment

            • klh
              #4
              Re: DB2, Java, and multi-row inserts

              I think I may be a victim of inconsistencies between the DB2 V8 LUW
              and DB2 V8 Z/OS.

              The multi-row insert:
              INSERT INTO DEPARTMENT
              (DEPTNO, DEPTNAME, ADMRDEPT)
              VALUES
              ('B11', 'PURCHASING', 'B01'),
              ('E41', 'DATABASE ADMINISTRATION' , 'E01') ;

              works on DB2 V8 LUW, but not DB2 V8 Z/OS.

              I thought since the multi-row insert was supported through the batch
              program syntax of:
              INSERT INTO test-table (col1, col2, col3)
              VALUES (:HV-ACT-NMBR-ARRAY,
              :HV-ACT-CITY-ARRAY,
              :HV-ACT-NAME-ARRAY )
              FOR 50 ROWS
              ATOMIC

              That there would be support for this using the V8 Type 4 drivers
              against DB2 V8 Z/OS.

              I thought the V8 was the release where IBM was trying to sync things
              up between Z/OS and LUW. However, I noticed in the DB2 9.5 LUW
              release that they mention having support for the multi-row insert, but
              it appears to be implicit via the "addBatch", and "executeBat ch" java
              statements.

              It appears I'll have to find out through trial and error what things
              work in DB2 V8 Z/OS.

              Thanks for your reply.
              klh

                Comment

                Previous template Next
                Working...