Folks

We are using hibernate 2.1.2 on Windows 2003 accessing a iseriesV5r2 DB2.

We had/have a problem with the following error occurring after a few hours.

[29/03/04 05:42:58:307 BST] 178496c4 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -904, SQLState: 57011
[29/03/04 05:42:58:307 BST] 178496c4 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0904] Resource limit exceeded.
[29/03/04 05:42:58:307 BST] 178496c4 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -904, SQLState: 57011
[29/03/04 05:42:58:307 BST] 178496c4 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0904] Resource limit exceeded.
[29/03/04 05:42:58:307 BST] 178496c4 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter Could not execute query
[29/03/04 05:42:58:307 BST] 178496c4 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: The following exception was logged java.sql.SQLException: [SQL0904] Resource limit exceeded.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:520)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java(Compiled Code))
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:256)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1881)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1823)
at com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:863)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.pmiPrepareStatement(WSJdbcConnection.java:1304)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java(Compiled Code))
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java(Compiled Code))
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java(Compiled Code))
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java(Compiled Code))
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java(Compiled Code))
at net.sf.hibernate.loader.Loader.doQuery(Loader.java(Compiled Code))
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java(Compiled Code))


I examined the code and noticed that the following query had a mixture of parameter setting styles. The commented out code was the original parameter settings. Since I changed the code so that it uses setParameter, everything seems to be ok(touch wood, fingers crossed).

I was wondering if anyone could clarify that this may indeed be the cause.

StringBuffer sb =
new StringBuffer("from uk.co.ltf.ltas.custinter.dao.ASMobileWorkOrder as amsl where D4OZCD= :supCustCode and D4WNS3 in (:status) and D4WFS3 in (:priority) and D4SCC2= :asStationCode and D4SOC2 !=:mobile ");

Query query = session.createQuery(sb.toString());

query.setParameter("supCustCode", mobileSC.getSuperCustomerCode(), Hibernate.STRING);
query.setParameter("asStationCode", mobileSC.getAsStationCode(), Hibernate.STRING);
query.setParameterList("status", status);
query.setParameterList("priority", priority);
query.setParameter("mobile", " ", Hibernate.STRING);


// the code below uses a mix of setting namedParameteres and JDBC style parameters
// this is not allowed- should use one or the other.
// PSR - I believe this may be the root cause of the sql0904 resource exceeded error
/*
query.setString("supCustCode", mobileSC.getSuperCustomerCode());
query.setString("asStationCode", mobileSC.getAsStationCode());
query.setParameterList("status", status);
query.setParameterList("priority", priority);
query.setString("mobile", " ");
*/

many thanks

Hi Paul,
I think it can be caused by "large" lists, probably generated SQL size exceeded limit, try to find some DB2 configuration option for "maxQuerySize" and increase it or rewrite query to use joins/subqueries if it can be unlimited.

Thanks for the advice.

I checked it out and the maximimum size for the iSeries DB2 sql statement is 32k- We don't get anywhere near that.

I have produced some debug traces and discovered that with the version that works (i.e using setParameter) the hibernate QueryTranslator goes through the compile query step.
The old version with a mixture of setString and setParameter does not appear to use this step..............????


Working code

09:23:34,530 DEBUG SessionImpl:1519 - iterate: from uk.co.ltf.ltas.custinter.dao.ASMobileWorkOrder as amsl where D4OZCD= :supCustCode and D4WNS3 in (:status0_, :status1_) and D4WFS3 in (:priority0_, :priority1_, :priority2_) and D4SCC2= :asStationCode and D4SOC2 !=:mobile
09:23:34,530 DEBUG QueryParameters:108 - named parameters: {priority2_=3, mobile= , asStationCode=LGW, supCustCode=2388, priority0_=1, status0_=20, priority1_=2, status1_=30}
09:23:34,540 DEBUG QueryTranslator:147 - compiling query
09:23:34,550 DEBUG QueryTranslator:199 - HQL: from uk.co.ltf.ltas.custinter.dao.ASMobileWorkOrder as amsl where D4OZCD= :supCustCode and D4WNS3 in (:status0_, :status1_) and D4WFS3 in (:priority0_, :priority1_, :priority2_) and D4SCC2= :asStationCode and D4SOC2 !=:mobile
09:23:34,550 DEBUG QueryTranslator:200 - SQL: select asmobilewo0_.D4SQC2 as x0_0_, asmobilewo0_.D4OZCD as x0_1_ from LTRAPF.TKD4CPP asmobilewo0_ where (D4OZCD=? )and(D4WNS3 in(? , ?))and(D4WFS3 in(? , ? , ?))and(D4SCC2=? )and(D4SOC2!=? )
09:23:34,550 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets


Problem code

09:01:10,381 DEBUG SessionImpl:1519 - iterate: from uk.co.ltf.ltas.custinter.dao.ASMobileWorkOrder as amsl where D4OZCD= :supCustCode and D4WNS3 in (:status0_, :status1_) and D4WFS3 in (:priority0_, :priority1_, :priority2_) and D4SCC2= :asStationCode and D4SOC2 !=:mobile
09:01:10,401 DEBUG QueryParameters:108 - named parameters: {priority2_=3, mobile= , asStationCode=LGW, supCustCode=2388, priority0_=1, status0_=20, priority1_=2, status1_=30}
09:01:10,401 DEBUG QueryTranslator:199 - HQL: from uk.co.ltf.ltas.custinter.dao.ASMobileWorkOrder as amsl where D4OZCD= :supCustCode and D4WNS3 in (:status0_, :status1_) and D4WFS3 in (:priority0_, :priority1_, :priority2_) and D4SCC2= :asStationCode and D4SOC2 !=:mobile
09:01:10,401 DEBUG QueryTranslator:200 - SQL: select asmobilewo0_.D4SQC2 as x0_0_, asmobilewo0_.D4OZCD as x0_1_ from LTRAPF.TKD4CPP asmobilewo0_ where (D4OZCD=? )and(D4WNS3 in(? , ?))and(D4WFS3 in(? , ? , ?))and(D4SCC2=? )and(D4SOC2!=? )
09:01:10,411 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets

Would be nice to know why

thank you

Google says this is fixed DB2 problem:
http://www-912.ibm.com/a_dir/as4ptf.nsf ... enDocument