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.

On Jul 19, 7:34*pm, Yonghang Wang <wyh... (AT) gmail (DOT) com> wrote:
How about loading from cursor?

Cheers,
Manoj Sutar

On Jul 20, 6:34*am, Yonghang Wang <wyh... (AT) gmail (DOT) com> wrote:
Hi Yonghang,

You can combine these 2 methods with batch queries as well:
http://publib.boulder.ibm.com/infoce..._tjvjcbqu.html

Sincerely,
Mark B.

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.

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.

On Aug 31, 7:31*am, Yonghang Wang <wyh... (AT) gmail (DOT) com> wrote:
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/infoce.../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.

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

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