we are using db2 ese on luw 9.5 fp8 on plinux
having a database with tablespaces of different page sizes and also temporary tablespaces of different page sizes.
we have a specific large table, residing in a 32k tablespace.
we run a query that returns a large result set. we are getting the message that the size of the temp.tablesize is hitting the physical limit (according the limits in the doc). but the query uses a 4k temp.tablespace. if it would have used a 32k, we should not have had this problem.
the question is : can we force, by a condition or setting.. that a query uses a larger page size for the temp.tablespace.
you can not drop the 4k tempspace, as it is the only available of this size.
any idea ? thanks for all help/comments

Please create a 32 K page Temp tablespace i.e. TEMPSYS32 and corresponding bufferpool with 32 K pagesize and see the response of the query from db2top.

From your post, it appears in your system only 4 K pagesize Tempspace is existing.

no - not correct
we have a tempspace of 4K 8k and 32k
we would have expected it would use the last one, but it is not.
why would we ask how to force this if none was available ? there would be no choice.

great that you have all the available pagesize tempspace. I came across similar issue last year when one of the user queries were using too much of tempspace with 4K Page, and we thought of creating a 32 K pagesize tempspace as the query was fetching huge number of records towards the tempspace and for that we created a separate bufferpool too. We ensured that the BP size was large enough and the tempspace with 32 K pagesize was having sufficient number of pages.

As much I remember, there was no way to force the optimizer to use a particular pagesize ( say 4K) tempspace. DB2 will prefer the one which can give a better cost.

so how do we overcome the problem - hitting the max limit size of a tablespace ?
this query can not be run anymore.

you can create the new 4K tablespace that is enough large for your query and drop the old small 4k tablespace.

as you can see in the doc : there is a physical limit of size in respect to a tablespace
the tempspace is sms on a filesystem of 1T which should be enough for this query
see : Table space page sizes
for 4k pages the tempspace is limited to 64G but for 32k the limit is 512G

Did you create a large enough bufferpool for the 32 K pagesize Tempspace so that the query can get tempted for the 32 K one. I will update more in case come across any other helpful info on this.

Try assigning a significantly larger bufferpool to the 32K system temporary tablespace than to the 4K tablspace - this should make optimizer prefer the former.

have you found a workaround?

I will try the suggestion of n_i "Try assigning a significantly larger bufferpo......"

What if you dropped the 4k temptablespace? DB2 would be forced to use the other ones...... Bit risky, so test 1st I'd say

this can NOT be dropped, as it is the only for that page size..

Are you sure 4K temp can't be dropped if you already have 32K temp?

on 9.5 the first default tempspace gets created with 4 K pagesize. So you can keep on creating other tempspace with higher pagesize say 8K / 16 K, but at any point at least one 4 K tempspace should exist.