I am a huge fan of always running a locking event monitor, and also using other event monitors when appropriate. This means that most databases I support have at least one event monitor, whether it is active or not. With some version changes, the structure of tables that event monitors write to are changed. This means that as you upgrade DB2, you must also run a command to upgrade your event monitors.
Errors About Event Monitors in DB2 Diagnostic Log
If you have event monitors that need to be upgraded, and you don’t upgrade, you might see errors like this in the DB2 diagnostic log:
2016-12-13-17.35.29.577771-300 I2854569A620 LEVEL: Error
PID : 51511410 TID : 2572 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-324 APPID: *LOCAL.db2inst1.
AUTHID : DB2INST1 HOSTNAME: host1
EDUID : 2572 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database monitor, sqm_evmon_mgr::autostartEvmons, probe:30
CALLED : DB2 UDB, database monitor, sqm_evmon_mgr::restartEvmons
RETCODE : ZRC=0x840D002A=-2079522774=SQLM_RC_MCMISS
"a mandatory column is missing"
2016-12-13-17.35.32.297641-300 E2855190A734 LEVEL: Warning
PID : 51511410 TID : 2572 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-324 APPID: *LOCAL.db2inst1.
AUTHID : DB2INST1 HOSTNAME: host1
EDUID : 2572 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database monitor, sqmSqlTarget::validateTable, probe:60
MESSAGE : ADM2014W The Event Monitor "LOCKEVMON" detected on table
"TAB_LOCKING" (ID "6") that the size of the column "EVENT_TYPE" is
smaller than the default size of "128". Therefore, contents will be
truncated to the user specified size.
Upgrading Event Monitors
IBM has provided a stored procedure to help us with this issue. EVMON_UPGRADE_TABLES to the rescue!
This stored procedure can be run for one or many event monitors to upgrade their SQL or unformatted target tables to the latest version. It takes three parameters as input, and provides three as output. It can be simply executed for everything after an upgrade using:
db2 "call evmon_upgrade_tables(NULL, NULL, NULL, ?, ?, ?)"
When run wide open like this, the stored procedure will look at SYSCAT.EVENTMONITORS and check each event monitor with a target of TABLE or UE (unformatted table) to see if it needs to be upgraded. There is a VERSIONNUMBER column in SYSCAT.EVENTMONITORS that contains either the version on which the event monitor was created or the version it was last upgraded to. If there are no changes between the previous version and the current version for an event monitor, the VERSIONNUMBER column will still be updated to indicate that it has been checked.
The ID used to execute this stored procedure must have DBADM authority on the database.
The first input parameter to the stored procedure can be used to specify the name of an event monitor, and take action only on that event monitor. The second input parameter specifies an event monitor type, and could be used to only take action on specific kinds of event monitors. The third input parameter is an options argument where xml specifying details of the event monitor upgrade can be specified.
EVMON_UPGRADE_TABLES will deactivate the event monitors and acquire an exclusive lock on their target tables. After all processing is complete, the lock will be released and the monitor reactivated.
Depending on what options are used, a temporary table called SESSION.EVMON_UPGRADE_TABLES_RESULTSET may be created with the results of the stored procedure. If problems are encountered, this table can be queried, but only in the same session as EVMON_UPGRADE_TABLES was called. The table is dropped when the session terminates or the connection is reset.
Like!!