The way I know is to look at SORT and TBSCAN above it and see if there is an increase in I/O.

How else can I tell if the optimizer may need to use tempspace?


Lots of tempspace is being used during query execution (static sql) and the access plan I have doesn't show any sort spills. Still need to get an explain for the query that is stored in the package (explain I have is for the same query that is stored in the package, but the query was run dynamically when db2exfmt was created). The only join method used is NLJOIN (no HSJOIN).

I have the package name / section # so I'll get the access plan stored in the package and compare to the one generated when it's run dynamically. The sql is filling up 170GB tempspace filesystem during execution... I'll confirm and recheck everything, but I definetely see it's spilling to disk. If you know of a way to identify tempspace usage from the access plan (other then SORT - TBSCAN), please let me know.

There are a lot of things that can spill beside sorts (e.g. HSJOIN), but I doubt you'll see it in the plan because the query compiler does not care about that - I think it's the interpreter that handles overflows. In other words, there's no information about the temp space use during compilation, only during execution.