Why does a db2agent return "No memory available in 'Database Monitor Heap'"?

Question & Answer

https://www.ibm.com/support/pages/why-does-db2agent-return-no-memory-available-database-monitor-heap

Question

If a locking event monitor is enabled in a database of that mon_heap_sz is small, some applications may return SQL0973N and following error can be observed in db2diag.log 2014-09-24-19.59.18.621288+480 I240532648A587 LEVEL: Error PID : 11075616 TID : 116873 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMPLE APPHDL : 0-21694 APPID: 10.2.105.103.20134.140913171301 AUTHID : TEST EDUID : 116873 EDUNAME: db2agent (CTEDB) 0 FUNCTION: DB2 UDB, relation data serv, sqlrr_activity_allocate_monheap, probe:30 RETCODE : ZRC=0x8B0F000B=-1961951221=SQLO_NOMEM_MONH "No memory available in 'Database Monitor Heap'" DIA8300C A memory heap error has occurred.

Cause

db2agent will start to execute functions including sqlrr_activity_allocate_monheap to collect information for event monitor, and these information will consume memory of database system monitor heap, and these functions may be failed in memory allocation if there is no enough memory space.

So please confirm value of mon_heap_sz to be set correctly (e.g. "automatic" may be good choice) before enable event monitor.

Answer

The opinion can be confirmed and observed by following sample.

1. Create a locking event monitor at the first.

######################################################################
db2sampl;
db2 update db cfg for sample using MON_LOCKTIMEOUT HIST_AND_VALUES
db2 update db cfg for sample using MON_DEADLOCK HIST_AND_VALUES
db2stop force;db2start

db2 connect to sample;
db2 "create event monitor lockinformation_monitor for locking write to unformatted event table (table t_lockinformation in USERSPACE1 pctdeactivate 80) manualstart"
######################################################################

2. If event monitor is being enabled, then function sqlrr_activity_allocate_monheap will be executed.

######################################################################
db2 connect to sample
db2 set event monitor lockinformation_monitor state 1
db2 terminate

db2 connect to sample
db2trc on -f db2trc.dump
db2 "select count(*) from customer"
db2trc off
db2trc flw db2trc.dump db2trc.flw
db2trc fmt db2trc.dump db2trc.fmt

grep sqlrr_activity_allocate_monheap db2trc.flw
######################################################################

$ grep sqlrr_activity_allocate_monheap db2trc.flw
23039 | | | | | | | | | | sqlrr_activity_allocate_monheap entry [eduid 1544 eduname db2agent]
23040 | | | | | | | | | | sqlrr_activity_allocate_monheap data [probe 1]
23044 | | | | | | | | | | sqlrr_activity_allocate_monheap exit

3. If the event monitor is disabled, then sqlrr_activity_allocate_monheap will be not executed at all.

######################################################################
db2 connect to sample
db2 set event monitor lockinformation_monitor state 0
db2 terminate

db2 connect to sample
db2trc on -f db2trc.dump
db2 "select count(*) from customer"
db2trc off
db2trc flw db2trc.dump db2trc.flw
db2trc fmt db2trc.dump db2trc.fmt

grep sqlrr_activity_allocate_monheap db2trc.flw
######################################################################

Nothing will be returned by "grep" command.