DB2 Memory Architecture

DB2 is very interesting database product which can grow in size as much you want, literally from couple MBs to Multi-Terabytes. Capacity totally depends on a) Disk where the real data will be stored, b) Memory allocation which is utilized to process the multi-terabytes of data to end users.

Lets talk how DB2 manages memory : DB2 memory is allocated in terms of shared memory and private memory. Shared memory is the one which is being shared by all of the db2 agents and gets allocated as soon as Instance/database is started. Whereas private memory is the one which is used by db agents individually.

Let's understand first about the shared memory : This is allocated at 3 different levels :
1. Instance
2. Database
3. Application

Instace level shared memory is allocated when its started, and its de-allocated as soon as you stop it. Its been controlled through following Instance configuration parameters :
MON_HEAP_SZ
AUDIT_BUF_SZ
FCM_NUM_BUFFERS

Database level shared memory is allocated when its either activated explicitly or with the first db connection, and de-allocated when its either deactivated explicitly or with the reset of last db connection. Following configuration parameters control the shared memory of database :
BUFFERPOOL
PACKAGE CACHE
CATALOG CACHE
LOCKLIST
DATABASE HEAP
UTILITY HEAP
SHARED SORT HEAP (if INTRA_PARALLEL is enabled)

Application level shared memory is not allocated in every situation. It gets allocated when the database agents (db connections) need to coordinate with each other. This happens in a specific scenario when you have either multi-partitioned database or have enabled the intra_parallel configuration in a single partitioned database. In these specific scenario, a single db agent forks the multiple subagents to serve the request in parallel but still share the same application memory which was allocated to their parent agent. Following configuration parameters control this shared memory :
APP_GROUP_MEM_SZ
GROUPHEAP_RATIO
APP_CTL_HEAP_SZ

Private memory is often referred as agent private memory. Its allocated through a database connection when its assigned to a database agent. Its usage is very dynamic and totally depends on how long that specific agent is busy in processing the request. This memory is utilized to perform sort,to acquire lock,
to build the access plan of SQL etc. Following configuration parameters are responsible for utilizing private memory :
APPLICATION HEAP
SORTHEAP
STATEMENT HEAP
STATISTICS HEAP
QUERY HEAP
JAVA INTERPRETER HEAP

You can monitor the usage of these memory allocation using db2 memory tracker ( db2mtrk) utility and you can keep re-adjusting based on the database size growth and its utilization.

Note:- This information is shared based on my knowledge and the experience.

Identify which db2 agent is consuming high CPU

Performance is a key thing in database world and its always a bottleneck area for OLTP system. If you happen to stuck in a scenario to identify which agentid is taking high CPU, here is the SQL :


select APPL.agent_id,appl_status,appl_con_time,
(( ( appl.agent_usr_cpu_time_s *1000000) + appl.agent_usr_cpu_time_ms) + ((appl.agent_sys_cpu_time_s * 1000000) + appl.agent_sys_cpu_time_ms)) CPU_Ms
,appl_name,primary_auth_id,client_nname,execution_id from
sysibmadm.snapappl APPL, sysibmadm.snapappl_info INFO where appl.agent_id=INFO.agent_id

It provides the cpu consumption in macro-seconds since the time it has connected.

Welcome to DB2SPECIALIST blog

I would be mostly blogging about DB2 on LUW. This is an amazing database to handle the database services and comparatively much cheaper than other database products available in market.

Wanna learn DB2 ?? No worries, IBM provides Express-C edition of DB2 just free of cost to all database enthusiast to explore this product. Download it and start using it.

Stat tuned for further updates...