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

How to load a cursor from a stored procedure in DB2

P: n/a
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)

Mar 21 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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
Mar 21 '06 #2

P: n/a
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

Mar 21 '06 #3

P: n/a
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
Mar 21 '06 #4

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

-Eugene

Mar 21 '06 #5

P: n/a
Well ,Thanks will look into that..

Mar 21 '06 #6

P: n/a
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
Mar 21 '06 #7

P: n/a
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

Mar 22 '06 #8

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

Mar 22 '06 #9

P: n/a
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

Mar 22 '06 #10

P: n/a
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
Mar 22 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.