By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,463 Members | 2,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,463 IT Pros & Developers. It's quick & easy.

why should temporary table space page size matter?

P: n/a

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)

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/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.
Nov 12 '05 #3

P: n/a


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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.