DB2 Upgrade Detail: Upgrading Event Monitor Tables

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- 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- 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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 557

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.