Hi All,

I am new to DB2 and I am getting the following error when I run a query:

could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryU tils.convertHibernateAccessException(SessionFactor yUtils.java:642)
at org.springframework.orm.jpa.vendor.HibernateJpaDia lect.translateExceptionIfPossible(HibernateJpaDial ect.java:92)
at org.springframework.orm.jpa.AbstractEntityManagerF actoryBean.translateExceptionIfPossible(AbstractEn tityManagerFactoryBean.java:352)
at org.springframework.dao.support.ChainedPersistence ExceptionTranslator.translateExceptionIfPossible(C hainedPersistenceExceptionTranslator.java:62)
at org.springframework.dao.support.DataAccessUtils.tr anslateIfNecessary(DataAccessUtils.java:212)
at org.springframework.dao.support.PersistenceExcepti onTranslationInterceptor.invoke(PersistenceExcepti onTranslationInterceptor.java:146)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy93.criteriaSearch(Unknown Source)
at com.symark.smc.service.impl.SimpleSearchCriteriaSe rviceImpl.getCriteriaResults(SimpleSearchCriteriaS erviceImpl.java:71)
at com.symark.smc.service.impl.SimpleSearchCriteriaSe rviceImpl.getCriteriaResults(SimpleSearchCriteriaS erviceImpl.java:84)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy174.getCriteriaResults(Unknown Source)
at com.symark.smc.service.AbstractSearchServiceTest.d oSearch(AbstractSearchServiceTest.java:48)
at com.symark.smc.service.AdminQueueServiceTest.searc h(AdminQueueServiceTest.java:131)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.test.context.junit4.SpringTest Method.invoke(SpringTestMethod.java:163)
at org.springframework.test.context.junit4.SpringMeth odRoadie****nTestMethod(SpringMethodRoadie.java:23 3)
at org.springframework.test.context.junit4.SpringMeth odRoadie$RunBeforesThenTestThenAfters****n(SpringM ethodRoadie.java:333)
at org.springframework.test.context.junit4.SpringMeth odRoadie****nWithRepetitions(SpringMethodRoadie.ja va:217)
at org.springframework.test.context.junit4.SpringMeth odRoadie****nTest(SpringMethodRoadie.java:197)
at org.springframework.test.context.junit4.SpringMeth odRoadie****n(SpringMethodRoadie.java:143)
at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.invokeTestMethod(SpringJUnit4ClassRu nner.java:142)
at org.junit.internal****nners.JUnit4ClassRunner****n Methods(JUnit4ClassRunner.java:51)
at org.junit.internal****nners.JUnit4ClassRunner$1*** *n(JUnit4ClassRunner.java:44)
at org.junit.internal****nners.ClassRoadie****nUnprot ected(ClassRoadie.java:27)
at org.junit.internal****nners.ClassRoadie****nProtec ted(ClassRoadie.java:37)
at org.junit.internal****nners.JUnit4ClassRunner****n (JUnit4ClassRunner.java:42)
at org.junit.internal****nners.CompositeRunner****nCh ildren(CompositeRunner.java:33)
at org.junit****nners.Suite.access$000(Suite.java:26)
at org.junit****nners.Suite$1****n(Suite.java:93)
at org.junit.internal****nners.ClassRoadie****nUnprot ected(ClassRoadie.java:27)
at org.junit.internal****nners.ClassRoadie****nProtec ted(ClassRoadie.java:37)
at org.junit****nners.Suite****n(Suite.java:91)
at org.eclipse.jdt.internal.junit4****nner.JUnit4Test Reference****n(JUnit4TestReference.java:46)
at org.eclipse.jdt.internal.junit****nner.TestExecuti on****n(TestExecution.java:38)
at org.eclipse.jdt.internal.junit****nner.RemoteTestR unner****nTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit****nner.RemoteTestR unner****nTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit****nner.RemoteTestR unner****n(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit****nner.RemoteTestR unner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledN onSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:223 1)
at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.criteria.CriteriaLoader.list( CriteriaLoader.java:118)
at org.hibernate.impl.SessionImpl.list(SessionImpl.ja va:1596)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl. java:306)
at com.symark.smc.core.dao.impl.jpa.PsmcSearchableDao Support.criteriaSearch(PsmcSearchableDaoSupport.ja va:69)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.dao.support.PersistenceExcepti onTranslationInterceptor.invoke(PersistenceExcepti onTranslationInterceptor.java:138)
... 48 more
Caused by: com.ibm.db2.jcc.am.SqlException: A system temporary table space with sufficient page size does not exist.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.57.86
at com.ibm.db2.jcc.am.bd.a(bd.java:679)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.d(km.java:2483)
at com.ibm.db2.jcc.am.km.a(km.java:1963)
at com.ibm.db2.jcc.t4.db.g(db.java:139)
at com.ibm.db2.jcc.t4.db.a(db.java:39)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
at com.ibm.db2.jcc.am.km.eb(km.java:1934)
at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
at com.ibm.db2.jcc.am.lm.Vb(lm.java:654)
at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:93 )
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:93 )
at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:69 7)
at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:222 8)
... 62 more

As you can see the root cause is "A system temporary table space with sufficient page size does not exist.."

Please find attached a summary of my tablespace and bufferpool information along with the query that errors. I am using the JDBC driver that comes with data studio 2.2 and it is a hibernate generated query that errors.

If there is anymore information I need to supply please let me know.

Any help would be greatly appreciated.

Thanks,
Mike

PS: cross posting from https://www.ibm.com/developerworks/f...341326� since I didnt get much a response yet and thought here might be better .

Although you have defined system temporary tables with 32K page size, your answer set is wider than 32K, which is the largest page size in DB2 (a system temporary table is sometimes needed to spool the results for sorting, etc).

Try using Lazy Loading in Hibernate:

There are two types of Loading in Hibernate. Eager loading and Lazy loading. Eager loading will fetch the parent and all child tables from the Persistent storage (DB2) and cache it. It will often cause serious performance issues. One can use lazy loading to avoid that scenario.

Usually it is not needed to automatically load all the child table values with the parent. In that case one can set lazy = true in .hbm file so that Hibernate will fetch only parent table values. Internally it will load the wrapper class but it does not cache all the child values until you perform that operation.

Advantage: It will avoid unnecessary joins and improve performance, and in your case keep you from doing so many joins that you exceed a result set wider than 32K.

Hi Marcus,

I was afraid of that this a is search query not a load operation and we use lazy everywhere (I dont think we a single eager collection). That just means that db2 is not capable of performing this search, which I find amazing since we have not any problems with other databases (mysql, postgres, oracle, sql server and derby). Are you sure there is nothing I can do to make it work in db2?

I will try to pare down the search query.

Thanks for you help.

-Mike

You could try and eliminate the need for a system temporary table. That might mean removing order by from the query (some other statements that require sorting may also use a temp table).

You might also try to cut down the width of the some of the columns with a substr or rtrim (not sure if this will help).

I have never seen this situaiton in a properly normalized database. In fact, I don't recall seeing anyone else complain about it at all (although maybe some have mentioned it and I don't recall).

Hi Marcus,

I think you were on the right track with the lazy thing, it turns out that the @OneToOne and @ManyToOne default to eager I am going to start with trying to change those without breaking my app.

Not everyone has the luxury of a properly normalized database :P

Those are all good suggestions that I will try if getting rid of the lazy doesnt work or breaks stuff.

Thanks again.

-Mike

This may be the only time in life when you will rewarded for being lazy.