Solved

User Temp Space Filling

Posted on 2006-07-11
7
995 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 
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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 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