DB2 Version 9.7 for Linux, UNIX, and Windows

MON_GET_TABLESPACE table function - Get table space metrics

The MON_GET_TABLESPACE table function returns monitor metrics for one or more table spaces.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MON_GET_TABLESPACE--(--tbsp_name--,--member--)--------------><

The schema is SYSPROC.

Table function parameters

tbsp_name
An input argument of type VARCHAR(128) that specifies a valid table space name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all table spaces in the database.
member
An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the MON_GET_TABLESPACE function.

Example

List table spaces ordered by number of physical reads from table space containers.

SELECT varchar(tbsp_name, 30) as tbsp_name, 
                  member, 
                  tbsp_type, 
                  pool_data_p_reads 
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t 
ORDER BY pool_data_p_reads DESC

The following example shows the output from this query.

TBSP_NAME                      MEMBER TBSP_TYPE  POOL_DATA_P_READS   
------------------------------ ------ ---------- --------------------
SYSCATSPACE                         0 DMS                          79
USERSPACE1                          0 DMS                          34
TEMPSPACE1                          0 SMS                           0

  3 record(s) selected.

Usage notes

The MON_GET_TABLESPACE table function returns one row of data per database table space and per database member. No aggregation across database members is performed. However, aggregation can be achieved through SQL queries.

Metrics collected by this function are controlled at the database level by using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.

Information returned

Table 1. Information returned for MON_GET_TABLESPACE
Column Name Data Type Description or corresponding monitor element
TBSP_NAME VARCHAR(128) tablespace_name - Table space name
TBSP_ID BIGINT tablespace_id - Table space identification
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_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_CUR_POOL_ID BIGINT tablespace_cur_pool_id - Buffer pool currently being used
TBSP_NEXT_POOL_ID BIGINT tablespace_next_pool_id - Buffer pool that will be used at next startup
FS_CACHING SMALLINT fs_caching - File system caching
TBSP_REBALANCER_MODE VARCHAR(30) tablespace_rebalancer_mode - Rebalancer mode. This interface returns a text identifier based on defines in sqlmon.h, and is one of:
  • NO_REBAL
  • FWD_REBAL
  • REV_REBAL
  • FWD_REBAL_OF_2PASS
  • REV_REBAL_OF_2PASS
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
DIRECT_READS BIGINT direct_reads - Direct reads from database
DIRECT_READ_REQS BIGINT direct_read_reqs - Direct read requests
DIRECT_WRITES BIGINT direct_writes - Direct writes to database
DIRECT_WRITE_REQS BIGINT direct_write_reqs - Direct write requests
POOL_DATA_L_READS BIGINT pool_data_l_reads - Buffer pool data logical reads
POOL_TEMP_DATA_L_READS BIGINT pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_XDA_L_READS BIGINT pool_xda_l_reads - Buffer pool XDA data logical reads
POOL_TEMP_XDA_L_READS BIGINT pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_INDEX_L_READS BIGINT pool_index_l_reads - Buffer pool index logical reads
POOL_TEMP_INDEX_L_READS BIGINT pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_DATA_P_READS BIGINT pool_data_p_reads - Buffer pool data physical reads
POOL_TEMP_DATA_P_READS BIGINT pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_XDA_P_READS BIGINT pool_xda_p_reads - Buffer pool XDA data physical reads
POOL_TEMP_XDA_P_READS BIGINT pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
POOL_INDEX_P_READS BIGINT pool_index_p_reads - Buffer pool index physical reads
POOL_TEMP_INDEX_P_READS BIGINT pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_DATA_WRITES BIGINT pool_data_writes - Buffer pool data writes
POOL_XDA_WRITES BIGINT pool_xda_writes - Buffer pool XDA data writes
POOL_INDEX_WRITES BIGINT pool_index_writes - Buffer pool index writes
DIRECT_READ_TIME BIGINT direct_read_time - Direct read time
DIRECT_WRITE_TIME BIGINT direct_write_time - Direct write time
POOL_READ_TIME BIGINT pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME BIGINT pool_write_time - Total buffer pool physical write time
POOL_ASYNC_DATA_READS BIGINT pool_async_data_reads - Buffer pool asynchronous data reads
POOL_ASYNC_DATA_READ_REQS BIGINT pool_async_data_read_reqs - Buffer pool asynchronous read requests
POOL_ASYNC_DATA_WRITES BIGINT pool_async_data_writes - Buffer pool asynchronous data writes
POOL_ASYNC_INDEX_READS BIGINT pool_async_index_reads - Buffer pool asynchronous index reads
POOL_ASYNC_INDEX_READ_REQS BIGINT pool_async_index_read_reqs - Buffer pool asynchronous index read requests
POOL_ASYNC_INDEX_WRITES BIGINT pool_async_index_writes - Buffer pool asynchronous index writes
POOL_ASYNC_XDA_READS BIGINT pool_async_xda_reads - Buffer pool asynchronous XDA data reads
POOL_ASYNC_XDA_READ_REQS BIGINT pool_async_xda_read_reqs - Buffer pool asynchronous XDA read requests
POOL_ASYNC_XDA_WRITES BIGINT pool_async_xda_writes - Buffer pool asynchronous XDA data writes
VECTORED_IOS BIGINT vectored_ios - Number of vectored IO requests
PAGES_FROM_VECTORED_IOS BIGINT pages_from_vectored_ios - Total number of pages read by vectored IO
BLOCK_IOS BIGINT block_ios - Number of block IO requests
PAGES_FROM_BLOCK_IOS BIGINT pages_from_block_ios - Total number of pages read by block IO
UNREAD_PREFETCH_PAGES BIGINT unread_prefetch_pages - Unread prefetch pages
FILES_CLOSED BIGINT files_closed - Database files closed
TBSP_STATE VARCHAR(256) tablespace_state - Table space state
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_USABLE_PAGES BIGINT tablespace_usable_pages - Usable pages in table space
TBSP_TOTAL_PAGES BIGINT tablespace_total_pages - Total pages in table space
TBSP_PENDING_FREE_PAGES BIGINT tablespace_pending_free_pages - Pending free pages in table space
TBSP_PAGE_TOP BIGINT tablespace_page_top - Table space high watermark
TBSP_MAX_PAGE_TOP BIGINT tbsp_max_page_top - Maximum table space page high watermark
RECLAIMABLE_SPACE_ENABLED SMALLINT reclaimable_space_enabled - Reclaimable space enabled indicator
AUTO_STORAGE_HYBRID SMALLINT auto_storage_hybrid - Hybrid automatic storage table space indicator
TBSP_PATHS_DROPPED SMALLINT tablespace_paths_dropped - Table space using dropped path
POOL_DATA_GBP_L_READS BIGINT Reserved for future use.
POOL_DATA_GBP_P_READS BIGINT Reserved for future use.
POOL_DATA_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_DATA_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_INDEX_GBP_L_READS BIGINT Reserved for future use.
POOL_INDEX_GBP_P_READS BIGINT Reserved for future use.
POOL_INDEX_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_INDEX_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_DATA_GBP_L_READS BIGINT Reserved for future use.
POOL_ASYNC_DATA_GBP_P_READS BIGINT Reserved for future use.
POOL_ASYNC_DATA_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_DATA_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_INDEX_GBP_L_READS BIGINT Reserved for future use.
POOL_ASYNC_INDEX_GBP_P_READS BIGINT Reserved for future use.
POOL_ASYNC_INDEX_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_INDEX_GBP_INVALID_PAGES BIGINT Reserved for future use.
TABLESPACE_MIN_RECOVERY_TIME TIMESTAMP Reserved for future use.
DBPARTITIONNUM SMALLINT In a partitioned database environment, this is the numeric identifier for the database member. For DB2® Enterprise Server Edition and in a DB2 pureScale® environment, this value is 0.
POOL_XDA_GBP_L_READS BIGINT Reserved for future use.
POOL_XDA_GBP_P_READS BIGINT Reserved for future use.
POOL_XDA_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_XDA_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_XDA_GBP_L_READS BIGINT Reserved for future use.
POOL_ASYNC_XDA_GBP_P_READS BIGINT Reserved for future use.
POOL_ASYNC_XDA_LBP_PAGES_FOUND BIGINT Reserved for future use.
POOL_ASYNC_XDA_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_ASYNC_READ_TIME BIGINT pool_async_read_time - Buffer pool asynchronous read time .
POOL_ASYNC_WRITE_TIME BIGINT pool_async_write_time - Buffer pool asynchronous write time .
TBSP_TRACKMOD_STATE VARCHAR(32) tbsp_trackmod_state - Table space trackmod state . This interface returns a text identifier, and is one of:
  • CLEAN
  • DIRTY
  • ININCREMENTAL
  • READFULL
  • READINCREMENTAL
  • UNAVAILABLE
ADDITIONAL_DETAILS BLOB(100K) Reserved for future use.
Parent topic: Monitor routines and views
Related concepts:
Monitor elements reported in monitor table functions