Solved

User Temp Space Filling

Posted on 2006-07-11
7
965 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how can I create a record layout for this file? 14 438
sql is not working correctly 23 353
db2 - u 3 225
SQL400 max size 5 57
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 (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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now