The MON_GET_MEMORY_POOL table function retrieves metrics from the memory pools contained within a memory set.
>>-MON_GET_MEMORY_POOL--(--memory_set_type--,--db_name--,--member--)-><
The schema is SYSPROC.
Value | Scope | Description |
---|---|---|
DBMS | Instance | DB2® database manager (DBM) memory set |
FMP | Instance | Fenced mode process (FMP) memory set |
PRIVATE | Instance | Private memory set |
DATABASE | Database | Database memory set |
APPLICATION | Database | Application memory set |
FCM | Host - there is only one FCM memory set allocated per machine for an instance. | Fast communication manager (FCM) memory set |
NULL | All | All memory sets at the instance and database level |
EXECUTE privilege on the MON_GET_MEMORY_POOL function.
Example 1: Retrieve memory set metrics for the current instance and the currently connected database.
SELECT varchar(memory_set_type, 20) AS set_type,
varchar(memory_pool_type,20) AS pool_type,
varchar(db_name, 20) AS dbname,
memory_pool_used,
memory_pool_used_hwm
FROM TABLE(
MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2))
An example of output from this query.
SET_TYPE POOL_TYPE DBNAME MEMORY_POOL_USED MEMORY_POOL_USED_HWM
------------ ---------------- ------------ ---------------- --------------------
DBMS FCM_LOCAL - 0 0
DBMS FCM_SESSION - 2359296 2359296
DBMS FCM_CHANNEL - 589824 589824
DBMS FCMBP - 983040 983040
DBMS FCM_CHANNEL - 35520512 35520512
DBMS MONITOR - 458752 589824
DBMS RESYNC - 262144 262144
DBMS OSS_TRACKER - 7667712 7667712
DBMS APM - 13041664 13238272
DBMS BSU - 3932160 4390912
DBMS KERNEL_CONTROL - 3932160 4390912
DBMS EDU - 655360 655360
FMP MISC - 655360 655360
DATABASE UTILITY TESTDB 65536 65536
DATABASE PACKAGE_CACHE TESTDB 983040 983040
DATABASE XMLCACHE TESTDB 196608 196608
DATABASE CAT_CACHE TESTDB 458752 458752
DATABASE BP TESTDB 850132992 850132992
DATABASE BP TESTDB 655360 655360
APPLICATION APPLICATION TESTDB 393216 393216
APPLICATION APPLICATION TESTDB 262144 262144
21 record(s) selected
In a partitioned database environment, the fast communication manager (FCM) memory set is allocated per host; all members on this host machine share this set. The MON_GET_MEMORY_POOL function retrieves data from each member. Since the FCM memory set is shared among all members on the host, the metrics reported for FCM memory for each member on the host represent information about the same shared memory set. For this reason, when examining metrics for FCM memory, examine the data for each unique host. For hosts with multiple members, use data from only one member on that host, as the metrics for FCM memory represent the aggregated total for all members on the given host.
Column name | Data type | Description |
---|---|---|
MEMBER | SMALLINT | member - Database member |
HOST_NAME | VARCHAR(255) | host_name - Host name |
DB_NAME | VARCHAR(128) | db_name - Database name |
MEMORY_SET_TYPE | VARCHAR(32) | memory_set_type - Memory set type. See the memory_set_type input parameter for the list of possible types. |
MEMORY_POOL_TYPE | VARCHAR(32) | memory_pool_type - Memory pool type. |
MEMORY_POOL_ID | BIGINT | memory_pool_id - Memory pool identifier |
APPLICATION_HANDLE | BIGINT | application_handle - Application handle. Only applicable to APPLICATION, STATISTICS, STATEMENT, and SORT_PRIVATE memory pool types. Otherwise, the value is NULL. |
EDU_ID | BIGINT | edu_id - Engine dispatchable unit identifier. Only applicable for memory pools allocated from the PRIVATE memory set type. Otherwise, the value is NULL. |
MEMORY_POOL_USED | BIGINT | memory_pool_used - Amount of memory pool in use |
MEMORY_POOL_USED_HWM | BIGINT | memory_pool_used_hwm - Memory pool high water mark |