How to check tablespace utilization in DB2 V9
This query can be used to check the space utilization for each table space,
including the current size, percentage free space, and size of free space.
For SMS table spaces, the buffer pool switch must be on to get results returned.
The SYSIBMADM.TBSP_UTILIZATION administrative view returns table space
configuration and utilization information. The view is an SQL interface for
the LIST TABLESPACES CLP command. Its information is based on the SNAPTBSP,
SNAPTBSP_PART administrative views and TABLESPACES catalog view.
db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \
substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \
smallint((float(tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100)as Percent_Free_Space, \
int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization"
The following is an example of the output for this query:
TABLESPACE_NAME TYPE STATUS SIZE_MEG PERCENT_FREE_SPACE MEG_FREE_SPACE
------------------------------ ---------- -------------------- -------------------- - ----------------------------- ---------------------
SYSCATSPACE DMS NORMAL 240 0 0
TEMPSPACE1 DMS NORMAL 100 99 99
USERSPACE1 DMS NORMAL 10240 32 3363
SYSTOOLSPACE SMS NORMAL 0 0 0
SYSTOOLSTMPSPACE SMS NORMAL 0 0 0
The query below lists tablespaces all DMS and their percentage used:
db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \
substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \
decimal((float(tbsp_total_size_kb - tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100,3,1)as Percent_used_Space, \
int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization where tbsp_type='DMS'"
Output
TABLESPACE_NAME TYPE STATUS SIZE_MEG PERCENT_USED_SPACE MEG_FREE_SPACE
------------------------------------------ ---------- -------------------- ----------- -------------------- ---------------------------------- ----------------------------------
SYSCATSPACE DMS NORMAL 64 61.1 24
USERSPACE1 DMS NORMAL 32 53.1 15
TBS32K DMS NORMAL 69996 77.8 15507
IDX_TBS DMS NORMAL 33619 99.0 315
SYSTOOLSPACE DMS NORMAL 32 0.2 31