Retrieving information from cluster caching facility memory usage monitor elements

You can use the MON_GET_CF table function to retrieve the various monitor elements that report on memory usage on the cluster caching facility in a Db2® pureScale® instance.

About this task

Understanding the extent to which memory in a cluster caching facility (also known as a CF) is used can help you to determine whether to adjust memory allocations. For example, if your cluster caching facility is using close to the maximum amount of allocated buffer pool memory, the hit rates for the group buffer pool might be lower than they could be. Or, if your lock memory is used to capacity, you might notice a higher-than-expected number of lock escalations. For more information on the MON_GET_CF table function, refer to ../../com.ibm.db2.luw.sql.rtn.doc/doc/r0056575.html.

Procedure

To retrieve information about memory usage in a cluster caching facility:

Determine which monitor elements you want to retrieve.
For example, if you want to view lock memory usage, you can choose from one or more of the following monitor elements:

Formulate a query using the MON_GET_CF table function.

Using the example from step 1, the statement would look like the following example:

SELECT SUBSTR(HOST_NAME,1,8) AS HOST,
       ID as HOSTID,
       CURRENT_CF_LOCK_SIZE,
       CONFIGURED_CF_LOCK_SIZE,
       TARGET_CF_LOCK_SIZE
  FROM TABLE( MON_GET_CF( NULL ) )

Run the query.
Continuing with this example, the output from the preceding query would resemble the following example:
```
HOST       HOSTID CURRENT_CF_LOCK_SIZE CONFIGURED_CF_LOCK_SIZE
---------- ------ -------------------- -----------------------
HOSTA         128               133852                  564224
HOSTB         129               133852                  564224

TARGET_CF_LOCK_SIZE
-------------------
             564224
             564224


  2 record(s) selected.
```
Note: The lock memory on both cluster caching facilities is typically the same, as one of the cluster caching facilities is duplexing its information and configuration to the other. You can check to see which of the two cluster caching facilities is the primary one by using one of the interfaces described in Viewing status information for members and cluster caching facilities in a Db2 pureScale instance. Examples of two such interfaces are the LIST INSTANCE and db2cluster commands.

Results

The preceding example shows that the current amount of lock memory used is 170,258 4k blocks, or 697,376,768 bytes. The maximum amount of lock memory available is 564,224 4k blocks, or 2,311,061,504 bytes.

Example

Example 1: Retrieving group buffer pool memory usage data.

The following query displays information about the group buffer pool memory size for all cluster caching facilities on the system:

SELECT SUBSTR(HOST_NAME,1,20) AS HOST,
       ID as HOSTID,
       CURRENT_CF_GBP_SIZE,
       CONFIGURED_CF_GBP_SIZE,
       TARGET_CF_GBP_SIZE
  FROM TABLE( MON_GET_CF(NULL) )

The following output is an example of what the preceding query returns:

HOST                 HOSTID CURRENT_CF_GBP_SIZE CONFIGURED_CF_GBP_SIZE TARGET_CF_GBP_SIZE
-------------------- ------ ------------------- ---------------------- ------------------
HOSTA                   128              367611                 500224             500224
HOSTB                   129              367611                 500224             500224

  2 record(s) selected.

In this example, the current GBP size is 367,611 4k pages, or 1,505,734,656 bytes. The memory allocated to the GBP is 500,224 4k pages, or 2,048,917,504 bytes.

Example 2: Retrieving shared communications area (SCA) memory usage data for a specific host.

The following query displays information about the SCA memory size for the cluster caching facility with the ID of 128:

SELECT SUBSTR(HOST_NAME,1,8)AS HOST,
       ID as HOSTID,
       CURRENT_CF_SCA_SIZE,
       CONFIGURED_CF_SCA_SIZE,
       TARGET_CF_SCA_SIZE   
FROM TABLE(MON_GET_CF(128))

The following output is an example of what the preceding query returns:

HOST     HOSTID CURRENT_CF_SCA_SIZE CONFIGURED_CF_SCA_SIZE
-------- ------ ------------------- ----------------------
HOSTA       128                  43                  16128

TARGET_CF_SCA_SIZE
------------------
             23280

   1 record(s) selected.

In this example, the SCA memory currently being used is 43 4k pages. At the point these monitor elements were retrieved, the maximum size of the SCA memory was 16,128 4k pages. However, the configured and the target sizes are the different, which means that the SCA memory is undergoing a size increase from the previous maximum configured size to 23,280 pages.

What to do next

In all the preceding examples, keep in mind that the values returned for memory usage provide only an overall view of memory usage. By themselves, they do not necessarily convey sufficient information to inform a decision about changing memory configuration. For example, the size of the group buffer pool (GBP), by itself, does not tell you whether it is large enough for the Db2 pureScale instance. In this case, consider using the monitor elements that report on buffer pool activity to calculate buffer pool hit rates. The hit rates can tell you whether you must adjust the size of your buffer pool. When it comes to lock memory, examining the number of lock escalations that take place gives you insight into whether enough lock memory has been allocated. A high rate of lock escalations can be an indicator that lock memory might need to be increased.