Managing the physical storage used by tables and indexes

Monitor the physical storage that is used by tables and indexes.

Procedure

Run the following SQL query to determine the physical storage used by table space containers in a database:

SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, INT(TBSP_ID) AS TBSP_ID, 
SUBSTR(CONTAINER_NAME,1,45) AS CONTAINER_NAME, INT(CONTAINER_ID) AS CONTAINER_ID, 
CONTAINER_TYPE, INT(TOTAL_PAGES) AS TOTAL_PAGES, INT(USABLE_PAGES) AS USABLE_PAGES,
ACCESSIBLE FROM SYSIBMADM.CONTAINER_UTILIZATION

If the storage usage is high, run the following SQL queries to identify the tables that use the most storage. For each query, the storage is returned in kilobytes. For example, storage that is used by tables with large object (LOB) columns can grow significantly over time.
1. Run the following SQL query to estimate the storage that is used by all the tables in a schema, where SCHEMA_NAME is the name of the schema:
```
select tabname,data_object_p_size from sysibmadm.admintabinfo where
tabschema='SCHEMA_NAME' order by tabname
```
2. Run the following SQL query to estimate the storage used by all the indexes in a schema:
```
select tabname,index_object_p_size from table(admin_get_index_info('','SCHEMA_NAME','')) 
group by tabname,index_object_p_size order by tabname
```
After pruning and reorganizing tables, you can run the following commands to release disk space to the storage system, where TS_NAME is the name of the table space:
```
alter tablespace TS_NAME reduce max;
alter tablespace TS_NAME LOWER HIGH WATER MARK
```

Run the following SQL query to determine the space in KB used by the transaction logs:

select char(DB_NAME,16) DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,
TOTAL_LOG_AVAILABLE_KB, TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION