MON_GET_MEMORY_SET table function - get memory set information
The MON_GET_MEMORY_SET table function retrieves metrics from the allocated memory sets, both at the instance level, and for all active databases within the instance.
Syntax
The schema is SYSPROC.
Table function parameters
- memory_set_type
- An input argument of type VARCHAR(32) that specifies the type of the memory set when calling this function. If the argument is NULL or an empty string, then metrics are retrieved for all memory sets at the instance and database level. Otherwise metrics for the specified memory set are retrieved.
- db_name
- An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database when calling this function.
- member
- An input argument of type INTEGER that specifies from which member the data is returned. Specify -1 for the current database member, or -2 for all active members. If the NULL value is specified, -1 is set implicitly.
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
None
Example
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(db_name, 20) as dbname,
memory_set_used,
memory_set_used_hwm
FROM TABLE(
MON_GET_MEMORY_SET(NULL, CURRENT_SERVER, -2))
An example of output from this query.
SET_TYPE DBNAME MEMORY_SET_USED MEMORY_SET_USED_HWM
------------ ---------------- --------------- -------------------
DBMS - 86080 87360
FMP - 0 704
PRIVATE - 10624 16256
DATABASE TESTDB 928000 928000
APPLICATION TESTDB 1472 2752
5 record(s) selected
Usage notes
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_SET 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.
Information returned
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_SET_ID | BIGINT | memory_set_id - Memory set identifier. |
MEMORY_SET_SIZE | BIGINT | memory_set_size - Memory set size. The value is in KB. |
MEMORY_SET_COMMITTED | BIGINT | memory_set_committed - Memory currently committed. The value is in KB. |
MEMORY_SET_USED | BIGINT | memory_set_used - Memory in use by this set. The value is in KB. |
MEMORY_SET_USED_HWM | BIGINT | memory_set_used_hwm - Memory set high water mark. The value is in KB. |