MON_GET_CONTAINER table function - Get table space container metrics

The MON_GET_CONTAINER table function returns monitor metrics for one or more table space containers.

Syntax

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 same database as the one currently connected to when calling this function. If the argument is null or an empty string, metrics are returned for all containers in 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 active database members. If the null value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned for MON_GET_CONTAINER
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
CONTAINER_NAME	VARCHAR(256)	container_name - Container name
CONTAINER_ID	BIGINT	container_id - Container identification
MEMBER	SMALLINT	member- Database member
CONTAINER_TYPE	VARCHAR(16)	container_type - Container type This is a text identifier based on the defines in sqlutil.h and is one of: DISK_EXTENT_TAG DISK_PAGE_TAG FILE_EXTENT_TAG FILE_PAGE_TAG PATH
STRIPE_SET	BIGINT	container_stripe_set - Stripe set
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_READ_TIME	BIGINT	direct_read_time - Direct read time
DIRECT_WRITE_TIME	BIGINT	direct_write_time - Direct write time
PAGES_READ	BIGINT	pages_read - Number of pages read
PAGES_WRITTEN	BIGINT	pages_written - Number of pages written
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
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
TOTAL_PAGES	BIGINT	container_total_pages - Total pages in container
USABLE_PAGES	BIGINT	container_usable_pages - Usable pages in container
ACCESSIBLE	SMALLINT	container_accessible - Accessibility of container
FS_ID	VARCHAR(22)	fs_id - Unique file system identification number
FS_TOTAL_SIZE	BIGINT	fs_total_size - Total size of a file system
FS_USED_SIZE	BIGINT	fs_used_size - Amount of space used on a file system
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element For a partitioned database environment, this will be the same value as for the MEMBER column. For Db2 Enterprise Server Edition and in a Db2 pureScale® environment, this value will be 0. Note: DBPARTITIONNUM is different to data_partition_id, which is used to identify a data partition that was created by subdividing data in a table based on a value.
DB_STORAGE_PATH_ID	BIGINT	db_storage_path_id - Storage path identifier monitor element
CACHING_TIER_DIRECT_READS	BIGINT	caching_tier_direct_reads - Direct reads from caching tier monitor element
CACHING_TIER_DIRECT_READ_REQS	BIGINT	caching_tier_direct_read_reqs - Cache direct read requests monitor element
CACHING_TIER_DIRECT_READ_TIME	BIGINT	caching_tier_direct_read_time - Cache direct read time monitor element

Usage notes

The MON_GET_CONTAINER table function returns one row of data per container and per database member. Data can be returned for all containers in a given table space, or for all containers in the database. No aggregation across database partitions 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.

Example

Example 1: List containers on all database members that have the highest read time.

SELECT varchar(container_name,70) as container_name, 
       varchar(tbsp_name,20) as tbsp_name, 
       pool_read_time 
FROM TABLE(MON_GET_CONTAINER('',-2)) AS t 
ORDER BY pool_read_time DESC

The following is an example of output from this query.

CONTAINER_NAME                                                         ... 
---------------------------------------------------------------------- ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000000/C0000000.CAT      ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000002/C0000000.LRG      ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000001/C0000000.TMP      ... 

  3 record(s) selected.

Output for query (continued).

... TBSP_NAME            POOL_READ_TIME      
... -------------------- --------------------
... SYSCATSPACE                           597
... USERSPACE1                             42
... TEMPSPACE1                              0

Example 2: List any containers that are not accessible.

SELECT varchar(container_name, 70) as container_name 
FROM TABLE(MON_GET_CONTAINER('',-1)) AS t 
WHERE accessible = 0

The following is an example of output from this query.

CONTAINER_NAME                                                        
----------------------------------------------------------------------

  0 record(s) selected.

Example 3: List utilization of container file systems, ordered by highest utilization.

SELECT varchar(container_name, 65) as container_name, 
       fs_id, 
       fs_used_size, 
       fs_total_size, 
       CASE WHEN fs_total_size > 0 
            THEN DEC(100*(FLOAT(fs_used_size)/FLOAT(fs_total_size)),5,2) 
            ELSE DEC(-1,5,2) 
       END as utilization 
FROM TABLE(MON_GET_CONTAINER('',-1)) AS t 
ORDER BY utilization DESC

The following is an example of output from this query.

CONTAINER_NAME                                                    ... 
----------------------------------------------------------------- ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000000/C0000000.CAT ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000001/C0000000.TMP ... 
/home/hotel55/swalkty/swalkty/NODE0000/TEST/T0000002/C0000000.LRG ... 

  3 record(s) selected.

Output for query (continued).

FS_ID                FS_USED_SIZE         FS_TOTAL_SIZE        UTILIZATION 
-------------------- -------------------- -------------------- ----------- 
               64768         106879311872         317068410880       33.70
               64768         106879311872         317068410880       33.70
               64768         106879311872         317068410880       33.70