Personal tools
You are here: Home DB2 DB2 UDB v9 How to check tablespace utilization in DB2 V9
Navigation
Log in


Forgot your password?
 
Document Actions

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


Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls