DB2 Version 10.1 for Linux, UNIX, and Windows

MON_GET_CF_CMD table function - Get processing times for cluster caching facility commands

The MON_GET_CF_CMD table function returns information about the processing times for cluster caching facility (CF) commands.

Syntax


>>-MON_GET_CF_CMD--(--id--)------------------------------------><

The schema is SYSPROC.

Table function parameters

id: An input argument of type INTEGER that specifies the identifier of the CF for which data is returned. You can use the DB2_CF administrative view to obtain the identifiers for the CFs in your DB2® pureScale® environment. If this parameter is NULL, information is returned for all CFs in your DB2 pureScale environment.

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

Example

The following statement is issued on a DB2 pureScale instance with multiple members and two cluster caching facilities with identifiers CF15 and CF16:

SELECT SUBSTR(HOSTNAME,1,8) AS HOST,
   SUBSTR(CF_CMD_NAME,1,20) AS CF_CMD_NAME,
   TOTAL_CF_REQUESTS AS REQUESTS 
FROM TABLE( MON_GET_CF_CMD(NULL) ) AS CFMETRICS
WHERE CF_CMD_NAME = 'SetLockState' OR CF_CMD_NAME = 'ReadAndRegister'

An example of output from this query is as follows:

HOST    CF_CMD_NAME         REQUESTS 
-----   -----------------   ---------
CF15    SetLockState        4
CF15    ReadAndRegister     7
CF16    SetLockState        1
CF16    ReadAndRegister     1

Usage notes

Data is returned only if you issue this function in a DB2 pureScale environment.

The reported values are provided by the CF and are independent of the instance level for a member or the number of databases that are active for a member.

The values that are returned in the TOTAL_CF_REQUESTS and TOTAL_CF_CMD_TIME_MICRO columns are calculated since cluster startup. You cannot reset the values; they are read-only.

You can calculate the average time by dividing the return value of the TOTAL_CF_CMD_TIME_MICRO column by the number returned by the TOTAL_CF_REQUESTS column.

Information returned

Table 1. Information that is returned for the MON_GET_CF_CMD function
Column name	Data type	Description or corresponding monitor element
HOSTNAME	VARCHAR(255)	hostname - Host name monitor element
ID	SMALLINT	id - Identification
CF_CMD_NAME	VARCHAR(128)	Name of the CF command.
TOTAL_CF_REQUESTS	BIGINT	Total number of requests in the CF for this command.
TOTAL_CF_CMD_TIME_MICRO	BIGINT	Total processing time in the CF for this command, in microseconds.

Table 2. List of CF commands that are monitored by the MON_GET_CF_CMD function
CF Command or alias name	Description
AllocationUnitRecovery	Recover a global lock manager (GLM) after a local lock manager (LLM) detaches, releasing all non-retained locks and downgrading retained locks.
AttachLocalCache	Attach to the group buffer pool (GBP).
CrossInvalidate \| XI	Send a single cross-invalidate message.
ExtendedMessageResponseBlock	Return the metric for a large message response block transmission along with the acknowledgment for the response transmission from the terminating side, as measured over the communication medium.
ExtendedMessageResponseBlockAsync	Return the metric for a large message response block transmission over the communication medium only. There is no waiting for an acknowledgment for the response transmission from the terminating side over the communication medium; completion of the transmission is checked separately.
LockNotification	Send a lock notification.
MessageResponseBlock	Return the metric for a message response block transmission along with the acknowledgment for the response transmission from the terminating side, as measured over the communication medium.
MessageResponseBlockAsync	Return the metric for a message response block transmission over the communication medium only. There is no waiting for an acknowledgment for the response transmission from the terminating side over the communication medium; completion of the transmission is checked separately.
ProcessSetLockStateExistingClient	Process the set lock state from an existing client.
ProcessSetLockStateExistingLock	Process the set lock state time for an existing lock.
ProcessSetLockStateNewClient	Process the set lock state from a new client.
ProcessSetLockStateNewLock	Process the set lock state time for a new lock.
ProcessSetLockStateSingleLock	Process the set lock state internal time for a single lock.
ReadAndRegister \| RAR	Read a single page from the CF.
ReadCacheInfo	Read the GBP cache information.
ReadCastoutClass	Read the list of pages in the castout class that are eligible to be cast out from the CF.
ReadCCInfo	Read the castout information for a range of castout classes from the CF.
ReadForCastout	Read data from the CF so that it can be cast out.
ReadForCastoutMultiple	Scan the castout queue in the CF for eligible entries. This is a combination of the ReadCastoutClass and ReadForCastout commands.
ReadForCastoutMultipleList	Scan the castout queue in the CF for eligible entries. This is a combination of the ReadCastoutClass and ReadForCastout commands for a specified list.
ReadLocks	Read the locks that are defined in the GLM structure in the CF.
ReadSA	Read the aggregate value from a smart array (SA) in the CF.
ReadSetLFS	Get or set a log flush sequence (LFS) number in the CF.
RecordLockState \| RLS	Record the lock state to the secondary CF.
RegisterPageList	Register a list of pages with the CF.
ReleaseCastoutLocks	Release castout locks on a page in the CF.
SetLockState \| SLS	Set the lock state of the CF.
TestPageValidity	Test whether the local page is valid in the CF.
TryInstant	Return the commands that are issued to obtain an instant lock from the CF.
WriteAndRegister \| WAR	Send a single-page image into the CF.
WriteAndRegisterMultiple \| WARM	Send a multiple-pages image into the CF.
WriteAndRegisterMultipleSubOperation	Suboperation of sending a multiple-pages image into the CF. This command returns the processing time of each page in the CF after the data was read from the communications medium for a WARM operation.
WriteSA	Write a value into an SA and retrieve the aggregate value in the CF.