Temporary table issue

[sandeep.iitk]

Hi,
One query is failing on the database and its a long query which was
running fine earlier. One possible reason we can think of is increase
in data in tables in query.
It is failing with following error:-
SQL0659N Maximum size of a table object has been exceeded.
SQLSTATE=54032 (SQLSTATE 54032) -659

Earlier we were having the temp file system size of around 60 GB. then
once query failed with the error "File system full"

so we increased it to 85 GB. now we got the above "Maximum size of a
table object has been exceeded" error.

My study suggest me that it is due to the temporarry table getting
bigger than 64 GB. Is SQL possess some limit on temporary table size
also as for normal tables it is 64 GB.
If Yes, is there any other way to increase the temp table size more
than 64 GB.

Thanks
Sandeep

[Ian]

sa**********@gm ail.com wrote:

Hi,
One query is failing on the database and its a long query which was
running fine earlier. One possible reason we can think of is increase
in data in tables in query.
It is failing with following error:-
SQL0659N Maximum size of a table object has been exceeded.
SQLSTATE=54032 (SQLSTATE 54032) -659

Earlier we were having the temp file system size of around 60 GB. then
once query failed with the error "File system full"

so we increased it to 85 GB. now we got the above "Maximum size of a
table object has been exceeded" error.

My study suggest me that it is due to the temporarry table getting
bigger than 64 GB. Is SQL possess some limit on temporary table size
also as for normal tables it is 64 GB.

Temporary tables share the same limits as normal tables. 64 Gb is the
limit for a 4kb page. Larger page sizes will have larger object limits;
so using an 8kb page should theoretically help your query complete.
Also note that this limit is per database partition, so if you're using
DPF, your limit increases with the number of database partitions.

However, I might also look at the query. If DB2 is building a 64 Gb
system temp table, maybe you should look at why it's doing that?
You may be able to solve your problem and improve performance at the
same time.

[sandeep.iitk]

HI Sir,
Thanks for such a fast reply. Just wanted to discuss you some issues
related to it.
In under consideration query, all tables are of 4K size but one which
is of 16 K size.
So I have some doubt:
Which temporary tablespace my query will use for sorting the data or
some intermediate process. (in my view it should use 16 K temp
tablespace, we are having both temp tablespace 4K and 16 K).
Waiting for reply
Thanks
Sandeep

[Ian]

sa**********@gm ail.com wrote:

Which temporary tablespace my query will use for sorting the data or
some intermediate process. (in my view it should use 16 K temp
tablespace, we are having both temp tablespace 4K and 16 K).

DB2 will use the tablespace with the smallest page size that can hold
the temporary table.

Note, just because a table requires a 16kb page, does not mean that
temporary tables resulting from queryies against that table require a
16kb page. Some might, but many won't.

[sandeep.iitk]

Hi Sir,

Thanks a lot :)