system temporary table space with sufficient page size does not exist

[BAbram]

Hi All,

System: DB2 8.2 (8.1.7b) PE

I have a problem with an error:

com.ibm.db2.jcc.b.SqlException: A system temporary table space with sufficient page size does not exist

..when executing a SQL query (only fetching the first 1000 rows).

How can I check that the current Database has a system temporary table space and that it's big enough (page size)?

Would I get all I need issuing a comman like this (Command Centre):

CONNECT TO [DATABASENAME];
LIST TABLESPACES SHOW DETAIL;

Would "managed space Contents = System Temporary data State" reveal that the tablespace in the list returned is a system temp table space? (I would get the page size in the info returned from the command)..

Many Thanks!
Bjorn

 

[gregsimpson]

When you create the database you get SYSCATSPACE,TEMPSPACE and USERSPACE1.

I suspect the data in yur query is of a different page size to that of your tempspace (probably larger). Post the full results of your list tablespaces command, so we can see what's going on.

In the meantime the following links may help.

http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0204b&L=db2-l&P=4261

http://www-1.ibm.com/support/docview.wss?uid=swg21201924

Cheers
Greg

 

[BAbram]

Thanks Greg!

Would it be enough to create another bigger system temp space for the database in question? (i.e. creating one with 32k page size?)

Or (according to the IBM link) must the temporary table space be of the very same size as the one used when creating the table? (Follow up question: What temp space will be used by the tables? One with a bigger page size or one with the same?)

Thanks Again,
Bjorn