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.
Authorization
This administrative view provides critical information for monitoring performance as well as space utilization.
This administrative view is a replacement for the TBSP_UTILIZATION administrative view.
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
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:
|
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:
|
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. |
COL_PHYSICAL_READS | BIGINT | Indicates the number of column-organized table data pages read from the physical table space containers for temporary, regular, and large table spaces. This is calculated as (pool_col_p_reads + pool_temp_col_p_reads) where pool_col_p_reads and pool_temp_col_p_reads represent the following monitor elements: |
COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | Column-organized table data hit ratio. The percentage of time that the database manager did not need to load a page from disk to service a column-organized table data page request. In Db2 pureScale environments, this value is the percentage of time that the database manager located a data page 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. |
GBP_COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | Group bufferpool column-organized table 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 column-organized table data request because the page was already in the group bufferpool. Outside of a Db2 pureScale environment environment, this value is null. |
CACHING_TIER_DATA_HIT_RATIO_PERCENT | DECIMAL(5,2) | Reserved for future use. |
CACHING_TIER_INDEX_HIT_RATIO_PERCENT | DECIMAL(5,2) | Reserved for future use. |
CACHING_TIER_XDA_HIT_RATIO_PERCENT | DECIMAL(5,2) | Reserved for future use. |
CACHING_TIER_COL_HIT_RATIO_PERCENT | DECIMAL(5,2) | Reserved for future use. |