File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ErrorCode -518 and SQLState 07003, to a StaleConnectionException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ErrorCode -518 and SQLState 07003, to a StaleConnectionException" Watch "ErrorCode -518 and SQLState 07003, to a StaleConnectionException" New topic
Author

ErrorCode -518 and SQLState 07003, to a StaleConnectionException

Sam Gehouse
Ranch Hand

Joined: Jul 21, 2003
Posts: 281
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

Joined: Sep 29, 2002
Posts: 1683
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.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ErrorCode -518 and SQLState 07003, to a StaleConnectionException
 
Similar Threads
How to read/write text file to db2 by using java code
CLI0112E Error in assignment. SQLSTATE=22005
Problem in writing file to db2 database
weird Db2exception
WAS 4.0.5 DB2 7.2 (NT) connection