Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Question Unanswered: A system temporary table space with sufficient page size does not exist w/ 32K pages

    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 .
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This may be the only time in life when you will rewarded for being lazy.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •