why should temporary table space page size matter?

[Joe Weinstein]

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)

[Gert van der Kooij]

In article <41**************@bea.com>, Joe Weinstein
(jo*******@bea.com) says...

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)

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.

[Mark A]

"Joe Weinstein" <jo*******@bea.com> wrote in message
news:41**************@bea.com...

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)

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.

[Joe Weinstein]

Gert van der Kooij wrote:

In article <41**************@bea.com>, Joe Weinstein
(jo*******@bea.com) says...

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)

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.

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