Mechanisms for batching up db2 sql statements in jdbc calls
Pradeep
I have a jdbc program that works with Oracle. I want to change the
program to make it work with db2. It has following pl/sql code.
String updateString =
"BEGIN "+
"DELETE FROM "+atable+" WHERE "+MyTable.URN_COL+"=?;"+
"DELETE FROM "+dtable+" WHERE "+MyTable.URN_COL+"=?;"+
"DELETE FROM "+ptable+" WHERE "+MyTable.URN_COL+"=?;"+
"END;";
pstmt.setString(1,toString());
pstmt.setString(2,toString());
pstmt.setString(3,toString());
In essence i want to batch up different dynamic sql statments and send
them to the db2 database. This way we make only one trip to the db
server.
My question is what are the different mechanisms in db2 or jdbc to
accomplish this?
I looked at the jdbc batch update mechanism . It seems like it will
work with a single dynamic statement (prepared statement) or multiple
static sql statements (Statement). I tried the following:
PreparedStatement prestmt = con.prepareStatement("delete from
tableA$
con.setAutoCommit(false);
prestmt.setInt(1,1);
prestmt.addBatch("delete from tableBwhere 1=?");
prestmt.setInt(1,1);
int [] updateCounts = prestmt.executeBatch();
con.commit();
con.setAutoCommit(true);
I got the following exception.
java.lang.NullPointerException
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.isElementNull(DB2PreparedStatement.java:3676)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.java:3379)
at DB2Test.main(DB2Appl.java:80)
I tried something different using db2 sql. (The sql statement works
when i ran it from db2 clp.)
PreparedStatement prestmt = con.prepareStatement("Begin Atomic delete
from tableA where 1=?; delete from tableB where 1=?; end");
prestmt.setInt(1,1);
prestmt.setInt(2,1);
ResultSet rs = prestmt.executeQuery();
I got the following exception.
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0637E QUERY
cannot be found.
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throwInvalidQueryError(SQLExceptionGenerator.java:585)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:985)
at DB2Test.main(DB2Appl.java:86)
I did use db2 jdbc 2.0 driver.
Thanks in advance for your replies.
-Pradeep
Dirk "db2scout" Wollscheid
"Pradeep" <gpkre...@yahoo.com> wrote in message
news:f9e374b4.02011...@posting.google.com...
> Hi all,
>
> I have a jdbc program that works with Oracle. I want to change the
> program to make it work with db2. It has following pl/sql code.
>
> String updateString =
> "BEGIN "+
> "DELETE FROM "+atable+" WHERE "+MyTable.URN_COL+"=?;"+
> "DELETE FROM "+dtable+" WHERE "+MyTable.URN_COL+"=?;"+
> "DELETE FROM "+ptable+" WHERE "+MyTable.URN_COL+"=?;"+
> "END;";
> pstmt.setString(1,toString());
> pstmt.setString(2,toString());
> pstmt.setString(3,toString());
>
> In essence i want to batch up different dynamic sql statments and send
> them to the db2 database. This way we make only one trip to the db
> server.
> My question is what are the different mechanisms in db2 or jdbc to
> accomplish this?
>
> I looked at the jdbc batch update mechanism . It seems like it will
> work with a single dynamic statement (prepared statement) or multiple
> static sql statements (Statement). I tried the following:
> PreparedStatement prestmt = con.prepareStatement("delete from
> tableA$
Did this got cutoff here? I don't see any closing >"< and "tableA$" isn't a
legal SQL ID (I think)
> con.setAutoCommit(false);
> prestmt.setInt(1,1);
> prestmt.addBatch("delete from tableBwhere 1=?");
> prestmt.setInt(1,1);
Why do you set another parameter after adding a statement to the batch? Not
sure if this is allowed...
> int [] updateCounts = prestmt.executeBatch();
> con.commit();
> con.setAutoCommit(true);
> I got the following exception.
> java.lang.NullPointerException
> at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.isElementNull(DB2PreparedStatement
.java:3676)
> at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.
java:3379)
> at DB2Test.main(DB2Appl.java:80)
Please fix the above things and try it again.This works in DB2!
> I tried something different using db2 sql. (The sql statement works
> when i ran it from db2 clp.)
> PreparedStatement prestmt = con.prepareStatement("Begin Atomic delete
> from tableA where 1=?; delete from tableB where 1=?; end");
> prestmt.setInt(1,1);
> prestmt.setInt(2,1);
> ResultSet rs = prestmt.executeQuery();
> I got the following exception.
> COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0637E QUERY
> cannot be found.
> at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throwInvalidQueryError(SQLExcepti
onGenerator.java:585)
> at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.
java:985)
> at DB2Test.main(DB2Appl.java:86)
From the SQL reference (under Compound SQL):
"This statement can only be embedded in an application program. The entire
Compound SQL statement construct is an executable statement that cannot be
dynamically prepared. "
Meaning: it's not possible to use this in a JDBC statement.
Dirk
Serge Rielau
You need V7 FP4 to get it to work.
The statement is called "compound statement (dynamic)"
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada
Pradeep
Dirk,
Thanks for your quick reply.
May be the code that works in oracle isnt clear enough. When i
substitute the values it would look something like this. Please notice
that they are all different SQL statements.
String updateString =
"BEGIN "+
"DELETE FROM ATABLE WHERE ACOL = ?;"+
"DELETE FROM BTABLE WHERE BCOL = ?;"+
"DELETE FROM CTABLE WHERE CCOL = ?;"+
"END;";
pstmt.setString(1,toString());
pstmt.setString(2,toString());
pstmt.setString(3,toString());
Here is the complete code for the first case.
PreparedStatement prestmt
= con.prepareStatement("delete from TableA where 1=?");
con.setAutoCommit(false);
prestmt.setInt(1,1);
prestmt.addBatch("delete from TableB where 1=?");//Note
different table
prestmt.setInt(1,1);
int [] updateCounts = prestmt.executeBatch();
con.commit();
con.setAutoCommit(true);
For case 2 with db2 specific sql i read in the following post that
with a fix i should be able to run this. I am currently on db2 7.2
with fix pack 5. Is
there a place where i can download that fix if it exists.
If oracle allows its version of dynamic compound sql to be executed
via jdbc, and jdbc allows any sql statement i am curious why db2
doesnt allow compound sql to be executed via its jdbc driver..
What is the proper way of doing what i am trying to do? Thanks in
advance.
-Pradeep
Dirk "db2scout" Wollscheid
quality it's also not updated often enough. At least not at the speed Serge
changes things! ;-)
But can actually you do something like this (that example doen't make sense,
but WTH?):
stmt=conn.prepareStatement("BEGIN ATOMIC"+
"SELECT col1 FROM tab1 WHERE col2=?"+
"SELECT col1 FROM tab2 WHERE col2=?"+
"SELECT col1 FROM tab3 WHERE col2=?"+
"END";
stmt.setString(1,"foo");
stmt.setString(2,"bar");
stmt.setString(3,"hugo");
Meaning: JDBC will figure out that it has to set the parameter markers in
the WHERE clause with "foo","bar" and "hugo"?
Just wondering... Dirk
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:3C489104...@ca.ibm.com...
Serge Rielau
parameter markers
wherever you please. SELECT, or VALUES statements in its body are of course only
useful if in a FOR loop,
assigned to local variables or if they have functions with external action in
their bodies.
Currently you cannot retun resultsets or values. They live for the sideeffects
they cause (e.g. UDI-operations).
Serge Rielau
It should work in V7 FP5. The reason why it didn't is that the DB2 client (CLI) has to do be aware of batching in general.
However the dynamic compound statement is NOT a CLI or JDBC batching. It's 100% one SQL statement (or rather one inline SQL PL stament)
(you can do a PREPARE on it in your application or procedure or type it from CLP).
The fix did nothing more than teach CLI to take its hand of when it sees 'BEGIN ATOMIC'.