I have been recently tasked with creation of an early warning solution for a memory condition from which some of our DB2 databases have been occasionally suffering. Once the condition is encountered, the only resolution is to restart the instance which can cause an impact.
While not a solution for the root cause, an early warning would make it possible for us to become aware of the condition and attempt to resolve it outside of business hours in a planned manner. We decided to use our monitoring platform, Foglight, to detect and issue alerts. Unfortunately, none of the data collections included with Foglight’s DB2 cartridge contain all of the data needed to determine if the condition is occurring.
Because of that, I had to create what Foglight calls a User Defined Collection. While I have been working on creating that UDC, I have happened on the COMM_PRIVATE_MEM monitoring element that is part of the data set returned by the SNAP_GET_DBM function. To me, the description of the element in DB2 Knowledge Center was not quite clear, and there is no mention of the units of the returned value. For anyone else who may be wondering about the same, the element contains the same data as in the PRIVATE field in the output of db2pd -dbptnmem
and the value returned by SNAP_GET_DBM is in bytes.
PS N:\> db2 "select comm_private_mem from table(sysproc.snap_get_dbm(-1)) as t WITH UR"
COMM_PRIVATE_MEM
--------------------
18481152
1 record(s) selected.
PS N:\> db2pd -dbptnmem
Database Member 0 -- Active -- Up 0 days 20:34:18 -- Date 2020-08-31-17.45.30.178000
Database Member Memory Controller Statistics
Controller Automatic: N
Controller License Limit: Y
Controller Limit Enforced: Y
Memory Limit: 5242880 KB
Current usage: 1897280 KB
HWM usage: 1897280 KB
Cached memory: 325760 KB
Individual Memory Consumers:
Name Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-SAMPLE 160064 160064 158592
DBMS-TEST 149120 14912 10304
FMP_RESOURCES 22528 22528 0
PRIVATE 18048 18048 2112
DB-SAMPLE 1547520 1547520 154752
PS N:\> 18481152 / 1KB
18048
PS N:\>