DB2 Version 9.7 for Linux, UNIX, and Windows

MON_TBSP_UTILIZATION - Retrieve monitoring metrics for all table spaces and all database partitions

The MON_TBSP_UTILIZATION administrative view returns key monitoring metrics, including hit ratios and utilization percentage, for all table spaces and all database partitions in the currently connected database. It provides critical information for monitoring performance as well as space utilization. This administrative view is a replacement for the TBSP_UTILIZATION administrative view.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7, this routine will no longer work.

Authorization

One of the following authorizations is required:

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

Information returned

Table 1. Information returned by the MON_TBSP_UTILIZATION administrative view
Column name	Data type	Description or Monitor element
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
MEMBER	SMALLINT	member- Database member
TBSP_TYPE	VARCHAR(10)	tablespace_type - Table space type. This interface returns a text identifier based on defines in sqlutil.h, and is one of: DMS SMS
TBSP_CONTENT_TYPE	VARCHAR(10)	tablespace_content_type - Table space content type. This interface returns a text identifier based on defines in sqlmon.h, and is one of: ANY LARGE SYSTEMP USRTEMP
TBSP_STATE	VARCHAR(256)	tablespace_state - Table space state
TBSP_PAGE_SIZE	BIGINT	tablespace_page_size - Table space page size
TBSP_EXTENT_SIZE	BIGINT	tablespace_extent_size - Table space extent size
TBSP_PREFETCH_SIZE	BIGINT	tablespace_prefetch_size - Table space prefetch size
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
TBSP_TOTAL_SIZE_KB	BIGINT	The total size of the table space in kilobytes. This is calculated as (`tablespace_total_pages` * `tablespace_page_size`) / 1024 where `tablespace_total_pages` and `tablespace_page_size` represent the following monitor elements: tablespace_total_pages - Total pages in table space tablespace_page_size - Table space page size
TBSP_USABLE_SIZE_KB	BIGINT	The total usable size of the table space, in kilobytes. This equals the total size of the table space minus the space used for overhead pages. This is calculated as (`tablespace_usable_pages` * `tablespace_page_size`) / 1024 where `tablespace_usable_pages` and `tablespace_page_size` represent the following monitor elements: tablespace_usable_pages - Usable pages in table space tablespace_page_size - Table space page size
TBSP_UTILIZATION _PERCENT	DECIMAL(5,2)	The utilization of the table space as a percentage. If `tablespace_usable_pages` is greater than zero, this is calculated as (`tablespace_used_pages` / `tablespace_usable_pages`) * 100 where `tablespace_used_pages` and `tablespace_usable_pages` represent the following monitor elements: tablespace_used_pages - Used pages in table space tablespace_usable_pages - Usable pages in table space NULL is returned if `tablespace_usable_pages` is not greater than zero.
TBSP_PAGE_TOP	BIGINT	tablespace_page_top - Table space high watermark
DATA_PHYSICAL_READS	BIGINT	Indicates the number of data pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_data_p_reads` + `pool_temp_data_p_reads`) where `pool_data_p_reads` and `pool_temp_data_p_reads` represent the following monitor elements: pool_data_p_reads - Buffer pool data physical reads pool_temp_data_p_reads - Buffer pool temporary data physical reads
DATA_HIT_ RATIO_PERCENT	DECIMAL(5,2)	Data hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request.
INDEX_PHYSICAL_READS	BIGINT	Indicates the number of index pages read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_index_p_reads` + `pool_temp_index_p_reads`) where `pool_index_p_reads` and `pool_temp_index_p_reads` represent the following monitor elements: pool_index_p_reads - Buffer pool index physical reads pool_temp_index_p_reads - Buffer pool temporary index physical reads
INDEX_HIT_RATIO _PERCENT	DECIMAL(5,2)	Index hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service an index data page request.
XDA_PHYSICAL_READS	BIGINT	Indicates the number of data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary as well as regular and large table spaces. This is calculated as (`pool_xda_p_reads` + `pool_temp_xda_p_reads`) where `pool_xda_p_reads` and `pool_temp_xda_p_reads` represent the following monitor elements: pool_xda_p_reads - Buffer pool XDA data physical reads pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
XDA_HIT_RATIO_PERCENT	DECIMAL(5,2)	Auxiliary storage objects hit ratio, that is, the percentage of time that the database manager did not need to load a page from disk to service a data page request for XML storage objects (XDAs).