I'm using DB2 v9.1 LUW with AIX. When you go to db2top and then type -T and then -L and get the total temp space used by the temp tables as in 640.0 K below:


db2top –T -L

Top 5 temp storage consumers (Total temp storage used 640.0K) │
│ ----------------------------------------------------------------------
│
│ Handle User Temp Size Percent Top5 640.0K [100.00%] │
│ ---------- --------------- ---------- -------- ------------------------- │
│ 1059 E1USER 640.0K 100.00% -------------------------



I wrote this SQL but it doesn't give the total temp size:

db2 "select *
from sysibmadm.snaptab a
where tab_type = 'TEMP_TABLE'

SNAPSHOT_TIMESTAMP TABSCHEMA TABNAME TAB_FILE_ID TAB_TYPE DATA_OBJECT_PAGES INDEX_OBJECT_PAGES LOB_OBJECT_PAGES LONG_OBJECT_PAGES XDA_OBJECT_PAGES ROWS_READ ROWS_WRITTEN OVERFLOW_ACCESSES PAGE_REORGS DBPARTITIONNUM TBSP_ID DATA_PARTITION_ID
-------------------- -----------------
2010-10-04-14.37.41.544923 <1059><E1USER > TEMP (00037,00002) 2 TEMP_TABLE 35 - - - - 0 3290 0 0 0 37 -

1 record(s) selected.


Could anyone help me with what table would be used to figure this out and how? Also it would be great to know what tables are included...

Thank you.


CC