The MON_GET_CONTAINER table function returns monitor metrics for one or more table space containers.
None
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
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.
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:
|
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 |