DB2 Version 9.7 for Linux, UNIX, and Windows

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


>>-MON_GET_MEMORY_SET--(--memory_set_type--,--db_name--,--member--)-><

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.

These parameter values are accepted:

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

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.

The database must have a directory entry type of either "INDIRECT" or "HOME", as returned by a LIST DATABASE DIRECTORY command. The database must be active. Alternatively, the CURRENT_SERVER special register can be specified to retrieve metrics from the currently connected database. The register value contains the actual name of the database, not an alias.

If the argument is NULL or an empty string, metrics are taken from all active databases in the instance. This input argument applies only to database level memory sets.

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 MON_GET_MEMORY_SET function.

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

Table 1. Information returned for MON_GET_MEMORY_SET
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.