Temporary table issue
in [DB2]
|
Prev: KUDCMA_DB2.exe errors in db2diag.log after applying 9.5 ESE FP2
Next: SQL27984W - Some information has not been saved (import create mode)
From: sandeep.iitk on 18 Sep 2008 10:16 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
From: Ian on 18 Sep 2008 12:07 sandeep.iitk(a)gmail.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.
From: sandeep.iitk on 18 Sep 2008 13:29 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
From: Ian on 18 Sep 2008 19:02 sandeep.iitk(a)gmail.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.
From: sandeep.iitk on 19 Sep 2008 05:09
Hi Sir, Thanks a lot :) |