I am connecting to DB2 using a datasource configured in WebSphere Application Server. Size of connection pool is 10 at this time.
I am trying to do a stress
test by inserting 10,000 records in a DB2 table.
Out of those 10,000 records, roughly 3,000 records successfully get inserted into database. Rest 7,000 records do not get inserted. I get the exception below:
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode -518 and SQLState 07003, to a StaleConnectionException: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:267)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java(Inlined Compiled Code))
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java(Compiled Code))
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java(Compiled Code))
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(DB2PreparedStatement.java(Compiled Code))
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java(Compiled Code))
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java(Compiled Code))
at com.mypackage.dao.MyDao.insert(MyDao.java(Compiled Code))
My code is as simple as:
-----------------
for(i = 0; i<msgNum; i++){
try{myDao.insert(recordToInsert);
}catch(Exception ex){
ex.printStackTrace();
}
}
-------------
insert method:
public void insert(
String recordToInsert) throws Exception {
Connection conn = null;
PreparedStatement statement = null;
try{
conn = ServerConnector.getConn();
StringBuffer insertSQLBuffer = new StringBuffer();
insertSQLBuffer.
append(" insert into ").
append("MYSCHEMA").
append(".").
append("MYTABLE").
append(" ( ").
append(" COL1, ").
append(" COL2, ").
append(" ) values ( ?, ?)");
PreparedStatement insertStatement = conn.prepareStatement(insertSQLBuffer.toString());
insertStatement.setString(1, "dummyVal1");
insertStatement.setString(1, "dummyVal2");
insertStatement.executeUpdate();
}
finally{
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
}
}
----------------------
I do not see this problem when I insert upto 1,000 records in the table. But I see abobe problem when I try to insert higher number (e.g. 10,000) records in the table.
Please note that I really insert into a table with BLOB column. Code above is just an idea, not the exact code. I try to insert 15KB files in a BLOB column that is scaled to hold up to 4MB file.