• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

ErrorCode -518 and SQLState 07003, to a StaleConnectionException

 
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic