• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

User Temp Space Filling

I have a temp tablespace filling quickly. What would the causes of this be?

Thank you.
~Bill L
0
billyleblanc
Asked:
billyleblanc
  • 4
  • 2
2 Solutions
 
db2inst1Commented:

Is it system or a user temp tablespace that is getting filled?

What is the size of your tempspace? I bielive its recommended to have 30% of the raw data size for OLAP env.

You should also be seeing the query that caused it in the db2diag.log at DIAGLEVEL 3. See if it can be tunned.
0
 
billyleblancAuthor Commented:
It's a user 8K temp tablespace. The size is 25000 pages. This is not an OLAP environment...

I think I know the query that is causing the issue ... but I'm trying to figure out why. Is it because the main table that the query is pulling from is very big and the join or sort causing the temp tablespace to fill? Is my sort heap too small and this is causing the temp space to fill? I'm not sure why a temp tablespace would fill.

My diag level is set at 3 and the query is not listed.

Thanks
0
 
ghp7000Commented:
temp space fills when load utility is being used or when sorts are overflowing or when temp tables are being built. If the space is too small, then it will fillquickly, if the space is large and still is filling up, eventually you will get error message from db2. Either increase the temp tablespace size or create another temp tablespace or tune your queries if possible and remove unnecessary order by.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
db2inst1Commented:

1.  Make sure you have the ulimit for instance owner set to ulimited for the filesize
2. Run an explain against the query for tablescans.
3. Run db2 index advisor against the query to avoid tablescans and sorts. (db2advis -d <dbname> -i query.sql)

Do you see any other relavent information in the db2diag.log when this happens. And does the query use "global temporary table"?
0
 
db2inst1Commented:
btw, you can use the index advisor output to determine if an index is required. You will have to update the statistics on the tables involved to get accurate results before running the advisor
0
 
billyleblancAuthor Commented:
How would I know if the query is using the "global temporary table"?

0
 
db2inst1Commented:
do you see a 'declare global temporary table' ?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now