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:

Upgrading Event Monitors

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 is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.