We are running servers at level DB2/6000 7.2.0 and DB2 OS/390 6.1.1
We are running windows and AIX clients at level DB2 SDK 7.2.0 and 7.1
with JDBC2.0
I have tried all the different permutations of updates (Statement
executeUpdate, PreparedStatement executeUpdate, Statement
executeBatch, PreparedStatement executeBatch). All of the updates
give the expected result (rc=1 for successful update or insert and
rc=0 for no update) except for prepared batch which always returns
rc=-2 on client level 7.2 or a rc=0 on client level 7.1, regardless of
whether the update was successful or unsuccessful. The only exception
to this is that an unsuccessful update from a 7.2 client to a 6.1.1
server generates an exception on prepared batch. All other types of
updates return a 0 in this case.
I have tried this using both jdk1.2.2 and jdk1.3.1 with identical
results.
I assume that the results returned from the prepared executeBatch are
not correct. Is there a work-around to allow me to differentiate
between successful and unsuccessful updates?
Here are the results of my test from a 7.2 client to 7.2 server. Each
test processes 5 inserts or updates. Return codes that are not equal
to 1 are printed out. The first run shows return codes from 5
successful inserts, the second run shows return codes from 5
successful updates, the last run shows return codes from 1 unsuccesful
update and 4 successful ones.
successful inserts
INSERT RECORDS
deleted=200
start of time
***TEST 0***
Statement executeUpdate
***TEST 1***
PreparedStatement executeUpdate
***TEST 2***
Statement executeBatch
updateCounts.length=5
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
successful updates
UPDATE RECORDS
deleted=0
start of time
***TEST 0***
Statement executeUpdate
***TEST 1***
PreparedStatement executeUpdate
***TEST 2***
Statement executeBatch
updateCounts.length=5
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
1 unsuccessful update
UPDATE RECORDS
deleted=0
start of time
***TEST 0***
Statement executeUpdate
3 rc =0
***TEST 1***
PreparedStatement executeUpdate
3 rc =0
***TEST 2***
Statement executeBatch
updateCounts.length=5
3=0
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
Here is the exception for unsuccessful update from 7.2 client to 6.1.1
server
java.sql.BatchUpdateException: [IBM][CLI Driver][DB2] SQL0301N The
value of a host variable in the EXECUTE or OPEN statement cannot be
used because of its data type. SQLSTATE=02000
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:255)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(SQLExceptionGenerator.java:803)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.java:2966)
at BatchTest.main(BatchTest.java:189)