DB2 Version 10.1 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.

Authorization

The schema is SYSIBMADM.

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

Default PUBLIC privilege

None

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:
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:
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:

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:
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:
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:
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). In a DB2® pureScale® environment, this value is the percentage of time the database manager used to locate a data page for an XDA in the local buffer pool.
GBP_DATA_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool data hit ratio. The percentage of time that the database manager did not need to load a page from disk in order to service a data page request because the page was already in the group bufferpool. Outside of a DB2 pureScale environment, this value is null.
GBP_INDEX_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool index hit ratio. The percentage of time that the database manager did not need to load a page from disk in order to service an index page request because the page was already in the group bufferpool. Outside of a DB2 pureScale environment, this value is null.
GBP_XDA_HIT_RATIO_PERCENT DECIMAL(5,2) Group bufferpool auxiliary storage object 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 object (XDAs) since the page was in the group bufferpool. Outside of a DB2 pureScale environment, this value will always be null.