By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,289 Members | 3,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,289 IT Pros & Developers. It's quick & easy.

DB2, Java, and multi-row inserts

P: n/a
klh
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
Apr 8 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"klh" <kh******@yahoo.comwrote in message
news:ea**********************************@d45g2000 hsc.googlegroups.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.
Apr 9 '08 #2

P: n/a
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/infoce...61%76%61%22%20
Apr 9 '08 #3

P: n/a
klh
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
Apr 10 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.