如何从DB2中的存储过程加载游标 [英] How to load a cursor from a stored procedure in DB2
问题描述
我正在尝试声明和光标并从该光标加载到另一个表格中。
。由于我有将近400万条记录，我无法做到这一点
没有光标，这会将时间减少近1/10。
我试图为a创建一个sql语句;从光标插入加载到
表名称使用java存储过程，但这不是由
sql识别的，因为load不是sql关键字。那么解决方案是什么呢。我需要以编程方式执行此操作，而不是每次都要使用db2clp
想要加载数据。
欢迎任何建议..
这是我正在尝试构建的代码的摘录
尝试
{
String sql =" declare cur *来自tablename的SELECT *;
PreparedStatement st = con.prepareStatement（sql）;
st.execute（sql）; //试过常规的Statement类也
}
catch（SQLException e）
{
System.out.println（声明游标时出错）;
e.printStackTrace（）;
}
尝试
{
String sql2 ="从游标插入到schema.customer中加载
nonrecoverable" ;;
语句st1 = con.createStatement（）;
st1.execute（sql2）;
}
catch（SQLException e1）
{
System.out.println（从光标加载时出错）;
}
在两个地方抛出异常。日志如下所示：
来自tablename的SELECT *
声明游标时出错
COM.ibm.db2.jdbc .DB2Exception：[IBM] [CLI Driver] [DB2 / 6000] SQL0104N
意外令牌从游标cur加载发现在
BEGIN-OF-STATEMENT之后。预期的令牌可能包括：< space>。
SQLSTATE = 42601
COM.ibm。 db2.jdbc.app.SQLExceptionGenerator.throw_S QLException（SQLExceptionGenerator.java（已编译
代码））
at
COM.ibm.db2。 jdbc.app.SQLExceptionGenerator.throw_S QLException（SQLExceptionGenerator.java:217）
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code（SQLExceptionGenerator.java ：449）
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）
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)
推荐答案
technocrat写道：
technocrat wrote:
尝试
{
String sql2 ="从游标插入的cur加载到schema.customer
不可恢复的;
语句st1 = con.createStatement（）;
st1.execute（sql2）;
}
try
{
String sql2 = "load from cur of cursor insert into schema.customer
nonrecoverable";
Statement st1=con.createStatement();
st1.execute(sql2);
}
LOAD不是SQL语句，而是DB2命令。因此，您不能使用任何JDBC方法（如Statement.execute（））来启动LOAD。您将需要使用DB2 API，这意味着类似于JNI，或者您需要将LOAD包装到一个过程中（如此处所做的那样 http://tinyurl.com/9gnlo
导入）并调用程序（ CALL是一个SQL语句）。还有一个另外的b $ b替代方法是创建一个SQL脚本并通过DB2 CLP执行该脚本。
- < br $>
Knut Stolze
DB2信息集成开发
IBM德国
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
Hi Knut，
感谢repply，我试着找到你给我的网址中的代码但是
i cudnt找到任何东西......你试图提及哪个imprt？我以某种方式在那里找到任何东西。
此外，你可以给我更多关于DB2API的见解吗？如何找到
以及我如何使用它，或者如果你有一个例子，如果你能把它发给我它
对我来说会非常有帮助。我已经花了差不多2天在
这个东西...
任何帮助都将不胜感激。谢谢
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
technocrat写道：
technocrat wrote:
嗨Knut，
感谢repply，我试过找网址中的代码给了我但是我在那里找到任何东西......你试图提及的是什么？我不知怎的在那找到任何东西。
代码位于db2migration.zip中。存档。看看
" truncate"过程。
此外，你可以给我更多关于DB2API的见解吗？如何找到
以及如何使用它，或者如果你有一个例子，如果你可以把它发给我它
对我来说会非常有帮助。我已经花了将近2天的时间来讨论这件事...
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...
DB2提供的完整API在此处记录： http://tinyurl.com/18r
-
Knut Stolze
DB2信息集成开发
IBM德国
The complete API provided by DB2 is documented here: http://tinyurl.com/18r
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
这篇关于如何从DB2中的存储过程加载游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
