Temporary tablespaces grow and shrink as required. They can be used for temporary tables, sorts, or certain types of joins. I still think you should start reading manuals.
There is a handy dandy utility in DB2 LUW (in at least version 9.7 and higher). I'm not sure when it exactly entered the product.
Sizing tables! And stored procedures that help calculate size.
First you need to run the stored procedure CREATE_STORAGEMGMT_TABLES, specifying the tablespace where you wish to put these (I recommend SYSTOOLSPACE). Once you have those you can run the CAPTURE_STORAGEMGNT_INFO stored procedure. It is generally recommended that you first run RUNSTATS on all tables with RUNSTATS ON TABLE (TABLESCHEMA.TABLENAME) ON KEY COLUMNS AND INDEXES ALL. Then run call capture_storagemgmt_info(0,<SCHEMA_NAME>,<DATABASE_NAME>) at least the first time (as per recommendation from IBM). After that you can run it for specific objects. This gives you the size in bytes for everything, tablespaces, tables, indexes, partitions, your database itself....
You do need to prune these tables manually as they will grow over time with use. (The reason is they just INSERT new snapshots of what sizes are.) The nice thing is you can use these to trend growth for your objects and even the whole database. If you wish to prune the tables, I tend to use delete from systools.stmg_root_object where stmg_timestamp < (current timestamp - 30 day). There is a cascading DELETE from that parent object (at least right now) that takes care of cleaning out all the child tables.
Here is an example of a report I run across this
select
t.main_tbspace as tablespace,
o.obj_schema as schema,
o.obj_name as table,
(max(t.estimated_size))/1024/1024 as maximum_size_in_mb,
(min(t.estimated_size))/1024/1024 as minimum_size_in_mb,
(avg(t.estimated_size))/1024/1024 as average_size_in_mb,
count(t.stmg_timestamp) as snapshots
from
systools.stmg_object as o,
systools.stmg_table as t
where
o.obj_id = t.obj_id and
o.stmg_timestamp = t.stmg_timestamp
group by t.main_tbspace,o.obj_schema,o.obj_name
order by maximum_size_in_mb desc, average_size_in_mb desc, minimum_size_in_mb desc
with UR"
And if you ever don't want the tables anymore, there is always the DROP_STORAGEMGTM_TABLES stored procedure.
Hope this helps!
Best Answer
The fact that you are using the
alter tablespace ... reduce maxsyntax indicates that you're using automatic storage (or DMS) tablespaces.Knowing that, truncating a table only frees up the space that the table occupies within a tablespace. (i.e., Db2 releases the extents that are assigned to the table.)
The reason Db2 can't just give back the space that was freed in the tablespace automatically is because there is no guarantee that the extents that were allocated to the table were located contiguously at the "end" of the tablespace.
Therefore, when you deallocate space in the tablespace (via
alter tablespace ... reduce max), Db2 must first "fill in the holes" by moving other in-use extents into the unused extents that were freed when you truncated the table.See the documentation for reclaimable storage for more details and some pictures of this.