DB2, Java, and multi-row inserts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

klh - 08 Apr 2008 20:04 GMT

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

Reply to this Message

Mark A - 09 Apr 2008 01:46 GMT

> We've just recently converted to DB2 V8 z/os for our backend database
> server (upgraded from DB2 V7).
[quoted text clipped - 33 lines]
> 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.

Reply to this Message

klh - 10 Apr 2008 15:54 GMT

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 "executeBatch" 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

Reply to this Message

Kalexin - 09 Apr 2008 17:47 GMT

> We've just recently converted to DB2 V8 z/os for our backend database
> server (upgraded from DB2 V7).
[quoted text clipped - 33 lines]
> Thanks,
> klh

JDBC supports batch update. Maybe this will be helpful
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ja
va/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

Reply to this Message