The STORAGE specification
The optional DROP STORAGE or REUSE STORAGE keywords specify what action the database server takes with the storage extents of the table when the TRUNCATE operation begins. If you omit this specification, the DROP STORAGE option is the default.
Using the default or explicit DROP STORAGE option, a successful
TRUNCATE statement releases all but the first extent among the extents
currently allocated to the table and to its indexes. You can display
the current list of extents with the oncheck -pT
table command.
If your table has only one extent, no space will be freed.
Extents
Logical Page Physical Page Size Physical Pages
0 1:104455 32 32
32 1:104495 4576 4576
oncheck
command
displays this: Extents
Logical Page Physical Page Size Physical Pages
0 1:104455 32 32
Alternatively, if you intend to keep the same storage space allocated to the same table for subsequently loaded data, specify the REUSE STORAGE keywords to prevent the space from being deallocated. The REUSE STORAGE option of TRUNCATE can make storage management more efficient in applications where the same table is periodically emptied and reloaded with new rows.
TRUNCATE TABLE state DROP STORAGE;
The
following example truncates the same table but removes only the actual
data. All extents stay the same. TRUNCATE TABLE state REUSE STORAGE;
Whether you specify DROP STORAGE or REUSE STORAGE, any out-of-row data values are released for all rows of the table when the TRUNCATE transaction is committed. Storage occupied by any BLOB or CLOB values that become unreferenced in the TRUNCATE transaction is also released.