DB2 Version 9.7 for Linux, UNIX, and Windows

TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information

The TBSP_UTILIZATION administrative view returns table space configuration and utilization information. It retrieve a similar report to the LIST TABLESPACES command on a single partitioned database. Its information is based on the SNAPTBSP, SNAPTBSP_PART administrative views and TABLESPACES catalog view.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the TBSP_UTILIZATION administrative view
CONTROL privilege on the TBSP_UTILIZATION administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Retrieve the same report as the LIST TABLESPACES command on a single partitioned database.

SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) as TBSP_NAME, TBSP_TYPE, 
   TBSP_CONTENT_TYPE, TBSP_STATE FROM SYSIBMADM.TBSP_UTILIZATION

The following example is a sample output for this query.

TBSP_ID     TBSP_NAME            TBSP_TYPE  ... 
-------...- -------------------- ---------- ... 
          0 SYSCATSPACE          SMS        ... 
          1 TEMPSPACE1           SMS        ... 
          2 USERSPACE1           SMS        ... 
          3 SYSTOOLSPACE         SMS        ... 
          4 SYSTOOLSTMPSPACE     SMS        ...

Output for this query (continued).

... TBSP_CONTENT_TYPE TBSP_STATE 
... ----------------- -----------
... ANY               NORMAL     
... SYSTEMP           NORMAL     
... ANY               NORMAL     
... ANY               NORMAL     
... USRTEMP           NORMAL

Information returned

Table 1. Information returned by the TBSP_UTILIZATION administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
TBSP_ID	BIGINT	tablespace_id - Table space identification
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TBSP_TYPE	VARCHAR(10)	tablespace_type - Table space type
TBSP_CONTENT_TYPE	VARCHAR(10)	tablespace_content_type - Table space content type
TBSP_CREATE_TIME	TIMESTAMP	Creation time of the table space.
TBSP_STATE	VARCHAR(256)	tablespace_state - Table space state
TBSP_TOTAL_SIZE_KB	BIGINT	The total size of the table space in KB, calculated as total_pages*pagesize/1024.
TBSP_USABLE_SIZE_KB	BIGINT	The total usable size of the table space in KB, calculated as usable_pages*pagesize/1024.
TBSP_USED_SIZE_KB	BIGINT	The total used size of the table space in KB, calculated as used_pages*pagesize/1024.
TBSP_FREE_SIZE_KB	BIGINT	The total available size of the table space in KB, calculated as free_pages*pagesize/1024.
TBSP_UTILIZATION_PERCENT	BIGINT	The utilization of the table space as a percentage. Calculated as (used_pages/usable_pages)*100, if usable_pages is available. Otherwise, -1 will be displayed.
TBSP_TOTAL_PAGES	BIGINT	tablespace_total_pages - Total pages in table space
TBSP_USABLE_PAGES	BIGINT	tablespace_usable_pages - Usable pages in table space
TBSP_USED_PAGES	BIGINT	tablespace_used_pages - Used pages in table space
TBSP_FREE_PAGES	BIGINT	tablespace_free_pages - Free pages in table space
TBSP_PAGE_TOP	BIGINT	tablespace_page_top - Table space high water mark
TBSP_PAGE_SIZE	INTEGER	tablespace_page_size - Table space page size
TBSP_EXTENT_SIZE	INTEGER	tablespace_extent_size - Table space extent size
TBSP_PREFETCH_SIZE	BIGINT	tablespace_prefetch_size - Table space prefetch size
TBSP_MAX_SIZE	BIGINT	tablespace_max_size - Maximum table space size
TBSP_INCREASE_SIZE	BIGINT	tablespace_increase_size - Increase size in bytes
TBSP_INCREASE_SIZE_PERCENT	SMALLINT	tablespace_increase_size_percent - Increase size by percent
TBSP_LAST_RESIZE_TIME	TIMESTAMP	tablespace_last_resize_time - Time of last successful resize
TBSP_LAST_RESIZE_FAILED	SMALLINT	tablespace_last_resize_failed - Last resize attempt failed
TBSP_USING_AUTO_STORAGE	SMALLINT	tablespace_using_auto_storage - Table space enabled for automatic storage
TBSP_AUTO_RESIZE_ENABLED	SMALLINT	tablespace_auto_resize_enabled - Table space automatic resizing enabled
DBPGNAME	VARCHAR(128)	Name of the database partition group for the table space.
TBSP_NUM_CONTAINERS	BIGINT	tablespace_num_containers - Number of containers in table space
REMARKS	VARCHAR(254)	User-provided comment.
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.