The MON_GET_CONTAINER table function returns monitor metrics for one or more table space containers.
EXECUTE privilege on the MON_GET_CONTAINER function.
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 example is a sample 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 example is a sample 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 example is a sample 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
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 using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.