How to load a cursor from a stored procedure in DB2

[technocrat]

Hi,
I am trying to declare and cursor and thn load from that cursor into
another table. Since I have almost 4 million records, I cant do it
without the cursor which reduces the time by almost 1/10th.
I tried to create a sql statement for "load from cursor cur insert into
table name" using java stored procedure, but this isnt recognised by
sql since load isnt a sql keyword. So whats the solution to this. I
have to do it programatically rahter than going to db2clp everytime I
want to load data.
Any suggestions are welcome..
This is the excerpt of the code I am trying to build
try
{
String sql="declare cur cursor for SELECT * from tablename;

PreparedStateme nt st= con.prepareStat ement(sql);
st.execute(sql) ; // tried regular Statement class also

}
catch (SQLException e)
{
System.out.prin tln("Error in declaring the cursor");
e.printStackTra ce();
}

try
{
String sql2 = "load from cur of cursor insert into schema.customer
nonrecoverable" ;
Statement st1=con.createS tatement();
st1.execute(sql 2);
}
catch (SQLException e1)
{
System.out.prin tln("Error loading form the cursor");
}

Throws exception at both the places. The log looks like this:

SELECT * from tablename
Error in declaring the cursor
COM.ibm.db2.jdb c.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0104N An
unexpected token "load from cur of cursor" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601

at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java(C ompiled
Code))
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java:2 17)
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.ch eck_return_code (SQLExceptionGe nerator.java:44 9)
at
COM.ibm.db2.jdb c.app.DB2Statem ent.execute2(DB 2Statement.java :857)
at
COM.ibm.db2.jdb c.app.DB2Statem ent.execute(DB2 Statement.java: 530)
at
PKG603011246378 44.InitControl. initCustomer(In itControl.java: 344)
at
PKG603011246378 44.InitControl. initControl(Ini tControl.java:4 9)

[Knut Stolze]

technocrat wrote:

try
{
String sql2 = "load from cur of cursor insert into schema.customer
nonrecoverable" ;
Statement st1=con.createS tatement();
st1.execute(sql 2);
}

LOAD is not a SQL statement but rather a DB2 command. Thus, you cannot use
any of the JDBC methods like Statement.execu te() to start a LOAD. You will
have to resort to the DB2 API, which implies something like JNI, or you
wrap the LOAD into a procedure (as was done here http://tinyurl.com/9gnlo
for import) and call the procedure (CALL is a SQL statement). Yet another
alternative would be to create a SQL script and execute that script through
the DB2 CLP.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[technocrat]

Hi Knut ,
thanks for the repply, i tried find the code in the url u gave me but
i cudnt find anything there...which imprt are u trying to mention? I
somehow dint find anything there.
Moreover can u give me more insight on the DB2API?? how to find that
and how can i use it or if u have an example if u can send it to me it
would be very helpfuil to me. I have already spent almost 2 days on
this thing...
Any help would be appreciated. Thanks

[Knut Stolze]

technocrat wrote:

Hi Knut ,
thanks for the repply, i tried find the code in the url u gave me but
i cudnt find anything there...which imprt are u trying to mention? I
somehow dint find anything there.
The code is in the "db2migration.z ip" archive. Have a look at the
"truncate" procedure.
Moreover can u give me more insight on the DB2API?? how to find that
and how can i use it or if u have an example if u can send it to me it
would be very helpfuil to me. I have already spent almost 2 days on
this thing...

The complete API provided by DB2 is documented here: http://tinyurl.com/18r

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Eugene F]

That's not going to work because DB2 LOAD command is not a SQL
statement and hence could not be driven by jdbc.

-Eugene

[technocrat]

Well ,Thanks will look into that..

[Serge Rielau]

create table tablename(c1 int);
insert into tablename values 1, 2, 3, 4, 5;
create table schema.customer like tablename;

CALL sysproc.db2load (1, 'declare cur cursor for SELECT * from tablename',
'load from cur of cursor insert into schema.customer
nonrecoverable' ,
?, '', ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL);

Enjoy
Serge

PS: And no... It's not documented.. but if it's good enough for the GUI
to use ....

PPS: Not sure.. may need FP9 or so...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[technocrat]

Any idea what I am supposed to enter for ?s.
This is the ideal way for me to implemetn since its a sql call but only
if i can know wht to enter for questionmarks
Any help appreciated

[technocrat]

well that worked..that was great..thanks a lot serge...i really
appreciate your help..!

[Eugene F]

Serge,

But *not documented* should also mean not supported by IBM, right? :-)
So how about using that solution at a customer site -- there are many
occasions where load from SQL would've made a project implementation
much easier.

Regards,
-Eugene