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;

PreparedStatement st= con.prepareStatement(sql);
st.execute(sql); // tried regular Statement class also

}
catch (SQLException e)
{
System.out.println("Error in declaring the cursor");
e.printStackTrace();
}

try
{
String sql2 = "load from cur of cursor insert into schema.customer
nonrecoverable";
Statement st1=con.createStatement();
st1.execute(sql2);
}
catch (SQLException e1)
{
System.out.println("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.jdbc.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.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGenerator.java(Compiled
Code))
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGenerator.java:217)
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGenerator.java:449)
at
COM.ibm.db2.jdbc.app.DB2Statement.execute2(DB2Stat ement.java:857)
at
COM.ibm.db2.jdbc.app.DB2Statement.execute(DB2State ment.java:530)
at
PKG60301124637844.InitControl.initCustomer(InitCon trol.java:344)
at
PKG60301124637844.InitControl.initControl(InitCont rol.java:49)

[Knut Stolze]

technocrat wrote:

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

LOAD is not a SQL statement but rather a DB2 command. Thus, you cannot use
any of the JDBC methods like Statement.execute() 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.zip" 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

[Serge Rielau]

Eugene F wrote:

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.

Do you believe in the Easterbunny? I do.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab