Question

Determining how much memory is actually used by DB2 db2sysc process and what is the maximum memory consumption limit, if any.

Cause

Tivoli Storage Manager server configures DB2 memory usage to AUTOMATIC by default, resulting in db2sysc process attempting to allocate a computed value ranging between 75 to 95 percent of physical memory. DB2 has self-tuning memory manager (STMM) which automatically adjusts memory allocation, based on the server workload, determined by DB2 monitors. If more than one instance is configured on the same physical system, then each instance will attempt to allocate up to 95 percent of the system memory. In this case we need to determine memory consumption and limits of each instance respectively, to set properly Tivoli Storage Manager server option DBMEMPERCENT for each instance.

Answer

To determine current memory usage of db2sysc process per instance, issue “db2pd -dbptnmem” as instance owner user.

Example (partial output is shown):

db2pd -dbptnmem

Database Partition 0 — Active — Up 1 days 00:41:43

Database Partition Memory Controller Statistics

Controller Automatic: Y
Memory Limit: 5449736 KB
Current usage: 1971392 KB
HWM usage: 1972544 KB
Cached memory: 505792 KB

Explanation:

  • Controller Automatic: Set to Y if the INSTANCE_MEMORY configuration parameter is set to AUTOMATIC. This means that database manager automatically determines the upper boundary on memory consumption.
  • Memory Limit: The DB2 instance upper bound of memory that can be consumed. It is the value of the INSTANCE_MEMORY configuration parameter. If INSTANCE_MEMORY is set to a specific value by setting Tivoli Storage Manager server option DBMEMPERCENT, then INSTANCE_MEMORY limit is enforced and the database manager allocates system memory up to this limit. System memory is not monitored. If INSTANCE_MEMORY is set to AUTOMATIC, then STMM updates the configuration to achieve optimal performance while monitoring available system memory and maintaining sufficient free instance memory.
  • Current usage: The amount of memory DB2 instance is currently consuming.
  • HWM usage: The high water mark (HWM) or peak memory usage that has been consumed since the activation of the database partition.
  • Cached memory: How much of the current usage is not currently being used, but is cached for performance reasons for future memory requests.

The amount of used instance memory can be determined by subtracting the cached memory value from current usage value.

Example:

1971392 KB (Current Usage) – 505792 KB (Cached Memory) = 1465600 KB (Used Memory)