Solved

User Temp Space Filling

Posted on 2006-07-11
7
972 Views
Last Modified: 2008-02-01
I have a temp tablespace filling quickly. What would the causes of this be?

Thank you.
~Bill L
0
Comment
Question by:billyleblanc
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

by:db2inst1
ID: 17084981

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
 

Author Comment

by:billyleblanc
ID: 17085395
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
 
LVL 13

Accepted Solution

by:
ghp7000 earned 250 total points
ID: 17085400
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 7

Assisted Solution

by:db2inst1
db2inst1 earned 250 total points
ID: 17085443

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
 
LVL 7

Expert Comment

by:db2inst1
ID: 17085462
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
 

Author Comment

by:billyleblanc
ID: 17085476
How would I know if the query is using the "global temporary table"?

0
 
LVL 7

Expert Comment

by:db2inst1
ID: 17085580
do you see a 'declare global temporary table' ?
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SAS Email Chart as attachment code 2 290
As400 Db2 to MS Sql server (data/schema) only 6 337
How do identify a "break in coveage" 8 182
I want coulmn name and value as output 18 66
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question