MON_GET_CF_WAIT_TIME table function - Get wait times for cluster caching facility commands
The MON_GET_CF_WAIT_TIME table function returns the total time measurement for cluster caching facility (CF) commands.
The time measurement returned by this function includes the network transport time to and from the CF and the execution time of the command within the CF.
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
Syntax
Table function parameters
The schema is SYSPROC.
- member
- An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database. Specify -1 for the current database member or -2 for all active members. If you specify the NULL value, -1 is set implicitly.
Information returned
| Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| MEMBER | SMALLINT | member - Database member |
| 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_WAIT_TIME_MICRO | BIGINT | Total wait time in the CF for this command, in microseconds. |
| CF Command or alias name | Description |
|---|---|
| DeletePage | Delete a page from the CF. |
| DeletePageList | Delete a list of pages from the CF. |
| DeleteSA | Delete an entry from a smart array (SA) in the CF. |
| DeleteSAList | Delete a list of entries from an SA in the CF. |
| DeregisterPage | Deregister a page from the CF. |
| DeregisterPageList | Deregister a list of pages from the CF. |
| GetAndIncLFS | Get and increment the log flush sequence (LFS) number in the CF. |
| GetLFS | Get an LFS from the CF. |
| GetLSN | Get the log sequence number (LSN) from the CF. |
| GetNotification | Return the commands issued to get notification messages from the global lock manager (GLM) in the CF. |
| GetStatus | Get the user control structures field from the CF. |
| GetUDF | Get the oldest page from the CF by using the user data field. |
| GLMDump | Request a dump of the GLM structure from the CF. |
| InitNotify | Return the commands issued to initialize message notification from the GLM structure in the CF. |
| MemberUD | Access the per member user data field in the CF. |
| ReadAndRegister | RAR | Read a single page from the CF. |
| ReadAndRegisterMultiple | RARM | Read multiple pages from the CF. |
| ReadCastoutClass | Read list of pages in the castout class that are eligible to be cast out from the CF. |
| ReadCCInfo | Read 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 command is a combination of the ReadCastoutClass and ReadForCastout commands. |
| ReadForCastoutMultipleList | Scan the castout queue in the CF for eligible entries. This command 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 an SA in the CF. |
| ReadSAList | Read all values for all SA variables or a particular SA variable in the CF. |
| ReleaseCastoutLocks | Release the castout lock on a page in the CF. |
| RecordLockState | RLS | Record the lock state to the secondary CF. |
| RecordLockStateAsync | Record the lock state asynchronously to the secondary CF. |
| RecordLockStateMultiple | RLSN | Record multiple lock states to the secondary CF. |
| RegisterPageList | Register a list of pages with the CF. |
| SetLFS | Set the LFS number in the CF. |
| SetLSN | Set the LSN in the CF. |
| SetLockState | SLS | Set the lock state to the CF. |
| SetLockStateAsync | Set the lock state asynchronously to the CF. |
| SetLockStateMultiple | SLSN | Set multiple lock states to the CF. |
| SetLockStateMultipleAsync | Set multiple lock states asynchronously to the CF. |
| SetStatus | Set the user control structures field in the CF. |
| TestPageValidity | Test whether a local page is valid in the CF. |
| TryInstant | Return the commands 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. |
| WriteSA | Write a value into an SA and retrieve the aggregate value in the CF. |
| WriteSAList | Write one or more SA variables in the CF. |
Usage notes
Data is returned only if you issue this function in a Db2® pureScale® environment.
This table function returns one row of data per command and per member.
The values are provided at the instance level for a member, and are independent of the number of databases active for a member, because a member is connected to a CF at the instance level.
The values that are returned in the TOTAL_CF_REQUESTS and TOTAL_CF_WAIT_TIME_MICRO return 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_WAIT_TIME_MICRO column by the number returned by the TOTAL_CF_REQUESTS column.
Example
To examine the rate at which page
deregistration occurs on a specific CF,
which is identified in the db2nodes.cfg file
as number 128, you want to compare values over a specific length of
time. To begin, issue the following statement:
SELECT TOTAL_CF_WAIT_TIME_MICRO AS DEREG_T1,
TOTAL_CF_REQUESTS AS DEREG_RQ1
FROM TABLE (MON_GET_CF_WAIT_TIME( -1 ))
WHERE CF_CMD_NAME = 'DeregisterPage' and ID = '128'This query returns the initial values for the comparison. Then, wait 10 seconds for the CF to deregister additional pages.
To get the updated values, issue
the following statement:
SELECT TOTAL_CF_WAIT_TIME_MICRO AS DEREG_T2,
TOTAL_CF_REQUESTS AS DEREG_RQ2
FROM TABLE (MON_GET_CF_WAIT_TIME( -1 ))
WHERE CF_CMD_NAME = 'DeregisterPage' and ID = '128'Finally,
calculate the rate at which the CF is
deregistrating pages by using the following formula:
(DEREG_RQ2 - DEREG_RQ1)
DEREG_PAGE_RATE = -----------------------
(DEREG_T2 - DEREG_T1)