Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1008
  • 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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