why should temporary table space page size matter?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Weinstein - 30 Oct 2004 06:34 GMT

Hi all. I am debugging a JDBC application. I find that a certain
query that has an order-by clause fails with a

SQL1585N A system temporary table space with sufficient page size does not exist.

I can understand if there is not enough room for the temp data for sorting,
but why should the page *size* matter?

thanks,
Joe Weinstein at BEA (DB2 ignorant)

Reply to this Message

Gert van der Kooij - 30 Oct 2004 11:21 GMT

> Hi all. I am debugging a JDBC application. I find that a certain
> query that has an order-by clause fails with a
[quoted text clipped - 6 lines]
> thanks,
> Joe Weinstein at BEA (DB2 ignorant)

It means the record is too large to fit on a page. If you only have
the regular temp tablespace with a 4K pagesize you need to create a
temp tablespace with 8K pagesize.

Reply to this Message

Joe Weinstein - 30 Oct 2004 16:50 GMT

>>Hi all. I am debugging a JDBC application. I find that a certain
>>query that has an order-by clause fails with a
[quoted text clipped - 10 lines]
> the regular temp tablespace with a 4K pagesize you need to create a
> temp tablespace with 8K pagesize.

Thanks much. I was musing on this possibility myself. It was a multi-table
select giving a wide row.
Joe

Reply to this Message

Mark A - 30 Oct 2004 14:22 GMT

> Hi all. I am debugging a JDBC application. I find that a certain
> query that has an order-by clause fails with a
[quoted text clipped - 6 lines]
> thanks,
> Joe Weinstein at BEA (DB2 ignorant)

Either one of the tables you are accessing has a page size bigger than 4K,
or you are joining multiple tables where the combined row length of the join
columns is bigger than 4K.

You should always have one system temporary tablespace (and corresponding
buffer pool) set up with a page size larger than 4K for such situations.
Most people make it 32K page size, but you could get away with 16K or 8K if
all (or almost all) of your row lengths are relatively small.

Reply to this Message