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-22.214.171.124000 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:\>
Luke is a member and, since 2015, Secretary of the Wisconsin DB2 Users’ Group.