is there any better way to insert into db2 tables from JDBC resultset or any other in memory data?

Yonghang Wang

unread,

Jul 19, 2011, 10:34:42 PM7/19/11

to

suppose I have a result set which contains data needed to be inserted
into a db2 tables, the obvious options are:
1. insert every row by a INSERT
2. group insert with "values(),(),," need to be careful to avoid the
sql limit of 2M.

any better idea? so far I can only rely on option 2.

Thanks.

Manoj Sutar

unread,

Aug 29, 2011, 5:19:56 PM8/29/11

to

How about loading from cursor?

Cheers,
Manoj Sutar

MarkB

unread,

Aug 30, 2011, 1:30:22 PM8/30/11

to

Hi Yonghang,

You can combine these 2 methods with batch queries as well:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_tjvjcbqu.html

Sincerely,
Mark B.

Yonghang Wang

unread,

Aug 30, 2011, 11:31:17 PM8/30/11

to

yes, thanks. it's interesting how much time it may save by prepared stmt. for this kind of workload, as the real executing part is only a INSERT.

just curious, is there anybody from IBM lab can show us whether it's possible to "declare cursor on JDBC ResultSet" or c/c++ alternatives.

Yonghang Wang

unread,

Aug 30, 2011, 11:33:51 PM8/30/11

to

Manoj, yes, it's the very thing I want to find... here the source is not db2 so we say the source is just JDBC resultset.

MarkB

unread,

Aug 31, 2011, 8:24:28 AM8/31/11

to

On Aug 31, 7:31 am, Yonghang Wang <wyh...@gmail.com> wrote:
> yes, thanks. it's interesting how much time it may save by prepared stmt. for this kind of workload, as the real executing part is only a INSERT.
>
> just curious, is there anybody from IBM lab can show us whether it's possible to "declare cursor on JDBC ResultSet" or c/c++ alternatives.

I'm not from the IBM lab but I can tell you that it's not possible :)
In CLI (but not in JDBC) you can use column-wise array input:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/t0007268.html
If you're able to save the contents of your ResultSet to some csv file
on the db2 server, you can load it with IMPORT/LOAD utilities via
ADMIN_CMD routine.

Serge Rielau

unread,

Aug 31, 2011, 9:11:53 AM8/31/11

to

Yonghang Wang,

You can create a procedure which takes an ARRAYs of scalar values as
input (you need to create type first).
Then do the INSERT within that procedure.

Something like this:

CREATE TYPE varchar_arr AS VARCHAR(4000) ARRAY[];

CREATE TYPE int_arr AS INTEGER ARRAY[];

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE insert_t(pk int_arr, val varchar_arr)
BEGIN
INSERT INTO t(pk, val) SELECT * FROM UNNEST(pk, val) AS T(pk, val);
END
@
--#SET TERMINATOR ;

JDBC can pass arrays to a CALL.
I think (?) in 9.7.4 you can also pass an array of rows.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Yonghang Wang

unread,

Aug 31, 2011, 9:34:25 PM8/31/11

to

thanks,really cool. This way I can declare the cursor for load.