Event monitor data retention from release to release

You can upgrade event monitor output tables after you upgrade the database product. This capability lets you retain any data that might exist in event monitor tables that you had before you upgraded.

As new capabilities are introduced in Db2®, the output for an event monitor type can change when a product update or upgrade is applied. The output tables for existing event monitors do not automatically change to reflect these new fields. Whenever new output is available, you can upgrade existing event monitor output tables using the EVMON_UPGRADE_TABLES stored procedure. Running the stored procedure captures the latest output and retains existing data in those event monitor tables.

In previous versions of Db2, if the EVMON_UPGRADE_TABLES stored procedure wasn't used, manual changes might have been required to update tables. For example, if you had existing event monitors that wrote to tables containing data that you wanted to retain, and you wanted to collect the data in some newly-added columns. In this case, you would need to manually alter the columns after upgrading to the new release. This alteration involved adding any of the new columns that you might want to use. If you did not add the new columns, the event monitor would work as it had in the previous release, capturing only the data supported by that the event monitor in that release.

Unformatted event tables that had changed could not be upgraded at all; you were required to drop them and then re-create them.

The EVMON_UPGRADE_TABLES stored procedure upgrades the definitions of existing event monitor tables to match those produced by the current level of the Db2 product. This feature lets you keep any existing tables that you might have, along with all the data they contain, eliminating the need to manually alter, or to drop and re-create tables.
Note: Starting in 10.1, you can also use the ALTER EVENT MONITOR statement to add new logical groups to an event monitor. You can use this approach as an alternative to EVMON_UPGRADE_TABLES to add logical data groups added in a new release. However, you cannot use ALTER EVENT MONITOR to modify logical groups that are already associated with the event monitor; if a logical data group already associated with the event monitor has changed, the only way to modify the event monitor is using the EVMON_UPGRADE_TABLES procedure.

The EVMON_UPGRADE_TABLES procedure works with both regular and UE tables. For regular tables, the procedure adds any new columns needed, drops old columns that are no longer required, and alters any columns as needed. For UE tables, the procedure adds new columns and modifies existing columns as needed to allow the UE table to be processed by the db2evmonfmt tool, or the EVMON_FORMAT_UE_TO_TABLES or EVMON_FORMAT_UE_TO_XML routines.

Important: Any active event monitors must be deactivated for the upgrade process to work properly. The EVMON_UPGRADE_TABLES procedure automatically deactivates any active event monitors before it begins upgrading tables. Do not reactivate any event monitors with tables being processed by EVMON_UPGRADE_TABLES, or the upgrade process will fail. Any event monitors that were active before the upgrade are activated again after the upgrade has completed.

Implications of not upgrading event monitor tables

As in past releases, you can choose to not upgrade your event monitor tables. However, any new columns that have been added to the event monitor in the new release will not be populated with data, and will not available for queries. Also, the values for any monitor elements that previously existed in the old release and that increased in size in the new release might be truncated. For example, if a monitor element increases in size from VARCHAR(20) to VARCHAR(128) in the new release, and you do not upgrade the previously-existing tables, the column that contains the monitor element values will continue to store only 20 characters of data, even though the system may be sending 128-bytes of data for that monitor element to the event monitor.

Upgrading tables produced by EVMON_FORMAT_UE_TO_TABLES

When used with UE tables, the EVMON_UPGRADE_TABLES procedure upgrades the UE table itself; it has no effect on any regular tables that you might have created using the EVMON_FORMAT_UE_TO_TABLES procedure. After you use EVMON_UPGRADE_TABLES to upgrade a UE table, you can also upgrade the output tables produced by EVMON_FORMAT_UE_TO_TABLES.  When you run the EVMON_FORMAT_UE_TO_TABLES procedure with the UPGRADE_TABLES option, any existing tables produced by the procedure are altered so that the table columns match the output produced by the new version of EVMON_FORMAT_UE_TO_TABLES procedure.