Question & Answer
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 : MYDB1
APPHDL : 0-21694 APPID: X.X.X.X.20134.140913171301
AUTHID : TEST EDUID : 116873 EDUNAME: db2agent (MYDB1) 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.
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.
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 Tools - db2support","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1;10.5;9.7;9.8","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
07 December 2022
UID
swg21685359