DB2 Table Spaces (TEMPSPACE1) and Joins

[putts]

I have a Join query that is hitting a table with about 4 million records and about two others that about 1.5 million each.

The query used to run fine but lately is beginning to really creep (about 13 minutes for 50000 records returned).

I started looking around Control Center hoping to trip across some hint as to why and found the Table Spaces entries.

Reading up on TEMPSPACE1 confirmed my theory that any JOIN query will need to build a Temporary table and that that temp table would be stored in this Table Space.

My thought is that I need to increase the Allocated Size (which is currently 1) but my boss believes that the size is dynamic and grows and contracts as need be.

Which is true and if it's not the size of the Table Space, is there something else I can look at to try to speed things up (maybe Buffer Pools or something else)

TIA.

 

[juliane26]

When it comes to physical things, please add the version and platform.
My answer for unix/windows:

If you create an own bufferpool just to serve the temp tablespace, that memory can be used only by the temp tablespace as well. So be careful.

I'd go around first checking the bufferpool for your data tablespaces (size and pagesize).
Do you have a temp tablespace using the same page size and same bufferpool like the data tablespaces?
On unix, the default install gives you only a temp tablespace using the 4K page size utilizing only the default bufferpool, which is not great.

Check on physical reads as well in the db snapshot.
Having physical read issues: to speed things up it can be useful to 'cash' data, like issuing some selects as a 'warm up' before starting the application.


Juliane