DB2 10.5 for Linux, UNIX, and Windows

MON_GET_CF table function - Get cluster caching facility metrics

The MON_GET_CF table function returns status information about one or more cluster caching facilities (also known as CFs) on the system.

Syntax


>>-MON_GET_CF--(--id--)----------------------------------------><

The schema is SYSPROC.

Table function parameters

id: An input argument of type INTEGER that specifies the identifier of the cluster caching facility for which data will be returned. You can use the DB2_CF administrative view to obtain the identifiers of the cluster caching facilities on your system. If this parameter is NULL, information for all cluster caching facilities on the system is returned.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Examples

The following query displays information about the group buffer pool size for all cluster caching facilities on the system. It shows a resize in progress for the group buffer pool. The CURRENT_CF_GBP_SIZE parameter shows the group buffer pool memory currently in use. The CONFIGURED_CF_GBP_SIZE parameter shows the group buffer pool memory currently allocated and reserved. The TARGET_CF_GBP_SIZE parameter shows the group buffer pool dynamic resize target.

SELECT SUBSTR(HOST_NAME,1,8) AS HOST,
       SUBSTR(DB_NAME, 1,8) AS DBNAME,
       CURRENT_CF_GBP_SIZE,
       CONFIGURED_CF_GBP_SIZE,
       TARGET_CF_GBP_SIZE
   FROM TABLE( MON_GET_CF( cast(NULL as integer) ) ) AS CAMETRICS
   ORDER BY HOST;

The following is an example of output from this query.

HOST  DBNAME  CURRENT_CF_GBP_SIZE CONFIGURED_CF_GBP_SIZE TARGET_CF_GBP_SIZE
----- ------- ------------------- ---------------------- ------------------
cf15  SAMPLE                 2402                   3000               5000
cf16  SAMPLE                 1276                   3000               5000

The next example returns information about the group buffer pool size for the cluster caching facility that has the identifier of 128.

SELECT SUBSTR(HOST_NAME,1,8) AS HOST,
       SUBSTR(DB_NAME, 1,8) AS DBNAME,
       CURRENT_CF_GBP_SIZE,
       CONFIGURED_CF_GBP_SIZE,
       TARGET_CF_GBP_SIZE
   FROM TABLE( MON_GET_CF( 128 ) ) AS CAMETRICS;

The following is an example of output from this query.

HOST  DBNAME CURRENT_CF_GBP_SIZE CONFIGURED_CF_GBP_SIZE TARGET_CF_GBP_SIZE
----- ------ ------------------- ---------------------- ------------------
cf16  SAMPLE                1276                   2000               2000

Usage notes

The MON_GET_CF table function returns one row of data per cluster caching facility-database pairing defined on the instance. No aggregation across cluster caching facilities or databases is performed.

Information returned

Table 1. Information returned for MON_GET_CF
Column name	Data type	Description or corresponding monitor element
HOST_NAME	VARCHAR(128)	host_name - Host name
ID	SMALLINT	id - Identification
DB_NAME	VARCHAR(128)	db_name - Database name
CURRENT_CF_GBP_SIZE	INTEGER	current_cf_gbp_size - Current CF group buffer pool size
CONFIGURED_CF_GBP_SIZE	INTEGER	configured_cf_gbp_size - Configured CF group buffer pool size
TARGET_CF_GBP_SIZE	INTEGER	target_cf_gbp_size - Target CF group buffer pool size
CURRENT_CF_LOCK_SIZE	INTEGER	current_cf_lock_size - Current CF lock size
CONFIGURED_CF_LOCK_SIZE	INTEGER	configured_cf_lock_size - Configured CF lock size
TARGET_CF_LOCK_SIZE	INTEGER	target_cf_lock_size - Target CF lock size
CURRENT_CF_SCA_SIZE	INTEGER	current_cf_sca_size - Current CF shared communication area size
CONFIGURED_CF_SCA_SIZE	INTEGER	configured_cf_sca_size - Configured CF shared communication area size
TARGET_CF_SCA_SIZE	INTEGER	target_cf_sca_size - Target CF shared communication area size
CURRENT_CF_MEM_SIZE	INTEGER	current_cf_mem_size - Current CF memory size
CONFIGURED_CF_MEM_SIZE	INTEGER	configured_cf_mem_size - Configured CF memory size