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

Read syntax diagramSkip visual syntax diagramMON_GET_CONTAINER(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 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
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
CACHING_TIER_DIRECT_READ_REQS BIGINT caching_tier_direct_read_reqs - Cache direct read requests monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.
CACHING_TIER_DIRECT_READ_TIME BIGINT caching_tier_direct_read_time - Cache direct read time monitor element
Note: This monitor element is currently only available for the Db2 Warehouse and Db2 Warehouse SaaS products.

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