Example: Investigating an increase in lock escalations using the change history event monitor

You can use the change history event monitor to detect what changes might have led to a database performance degradation.

Scenario

In this example, users are reporting a decline in database performance. The database administrator (DBA) notices an abnormally high number of lock escalations occurred in the last 24 hours. The DBA also notices a corresponding increase in application lock wait times over the same period.

The DBA has been monitoring configuration changes, index changes, and LOAD operations with a change history event monitor. The event monitor was created with the following statement:

    CREATE EVENT MONITOR CFGHIST 
    FOR CHANGE HISTORY WHERE EVENT IN (DBCFG, DBMCFG, DBCFGVALUES,
         DBMCFGVALUES,REGVAR,REGVARVALUES, DDLDATA, LOAD)
    WRITE TO TABLE

The event monitor was activated with the following statement:

    SET EVENT MONITOR CFGHIST STATE=1

The following table shows some sample event monitor data that the CFGHIST change history event monitor might write to the CHANGESUMMARY_CFGHIST table. All change history event monitors write to the CHANGESUMMARY logical data group. As described in CHANGESUMMARY logical data group , the CHANGESUMMARY logical data group returns a number of event monitor elements that summarize the events captured, only a subset of those elements are shown in the following output. The table name is derived by concatenating the name of the logical data group used to populate the table (CHANGESUMMARY) with the name given to the event monitor in the CREATE EVENT MONITOR statement (CFGHIST).

APPL_ID                      APPL_NAME .... EVENT_ID EVENT_TIMESTAMP    
---------------------------- --------- .... -------- -------------------
*LOCAL.tripathy.111028110756 db2bp     .... 1        28/10/2011 07:12:02  

EVENT_TYPE MEMBER ....
---------- ------ ....
EVMONSTART 0      ....

Since performance was not previously a problem, the DBA suspects that some recent change might be causing the problem and performs the following steps:

Check the CHANGESUMMARY logical data group for any changes made in the last 24 hours. For this example, assume that the current time is 31/10/2011 06:00:00.
```
 SELECT EVENT_TYPE FROM CHANGESUMMARY_CFGHIST
    WHERE EVENT_TIMESTAMP > CURRENT TIMESTAMP - 24 HOURS
```
The query returns the following result:
```
  EVENT_TYPE
  ----------
  DBCFG
  DBCFG
```
The output indicates that there were two database configuration updates in the last 24 hours.
Query the DBDBMCFG logical data group to get the details of the configuration changes.

 SELECT EVENT_TIMESTAMP, CFG_NAME, CFG_VALUE, CFG_OLD_VALUE, DB_DEFERRED
    FROM DBDBMCFG_CHGHIST

The query returns the following result:

  EVENT_TIMESTAMP       CFG_NAME      CFG_VALUE   CFG_OLD_VALUE   DB_DEFERRED
  -------------------   -----------   ---------   -------------   -----------
  30/10/2011 08:41:39   LOCKLIST           1024            2048   N 
  30/10/2011 08:42:35   LOCKTIMEOUT           0              -1   Y

The output indicates that locking changes were made during the time period when performance declined.

The DBA notices that the LOCKTIMEOUT change was deferred and issues a query to check whether the database was activated after this configuration change. This check determines whether the configuration change was picked up by the database. If the change was not picked up, then it is unlikely that it is causing the performance problem. The database activation time is recorded in the EVMONSTART logical data group. All change history event monitors write to the EVMONSTART logical data group by default.

   SELECT COUNT (*)as POST_CFG_ACTIVATIONS FROM EVMONSTART_CHGHIST
      WHERE DB_CONN_TIME > TIMESTAMP(2011-10-30-08:42:35)

The query returns a nonzero value.

  POST_CFG_ACTIVATIONS
  --------------------
                     1

This nonzero value confirms that the database was activated after the LOCKTIMEOUT configuration parameter was changed, meaning that the new value is in effect. The DBA now understands what changed on the system and can try adjusting the lock-related configuration parameters back to their original values to see whether this resolves the issue.

Note: If the change history event monitor was inactive when the configuration parameters were changed, then the event monitor would not capture the DBCFG events. Instead the change history event monitor would capture a DBCFGVALUES event when the event monitor was started. In the DBDBMCFG logical data group each row represents a configuration parameter that was updated as part of a DBCFG or DBMCFG event, or captured at event monitor startup as part of a DBCFGVALUES or DBMCFGVALUES event. The CFG_COLLECTION_TYPE monitor element identifies whether the record describes a configuration parameter update, or an initial value recorded at event monitor startup. The DBA would need to compare the values captured at the start of the current change history event monitor with the values of the previous capture to look for changed values that might be causing the problem. Examining the diagnostic log would also be helpful.