• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ErrorCode -518 and SQLState 07003, to a StaleConnectionException

 
Sam Gehouse
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My suspicion is that the database server is struggling to keep up with the requests. Test this by putting in a small delay between requests.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic