Example: Reporting the history of utility execution using the change history event monitor

You can use the change history event monitor to track utility operations performed on a database.

Scenario

In this example, the database administrator (DBA) wants to capture and list the utility event executions on a database. To report utility events:

Create a change history event monitor that tracks utility events. For example:

 CREATE EVENT MONITOR MON_UTIL
    FOR CHANGE HISTORY WHERE EVENT IN (UTILALL)
    WRITE TO TABLE
       CHANGESUMMARY (TABLE UTIL_COMMON),
       UTILSTART (TABLE UTIL_START),
       UTILSTOP (TABLE UTIL_STOP),
       UTILLOCATION (TABLE UTIL_LOCATION),
       UTILPHASE (TABLE UTIL_PHASES) AUTOSTART

Enable the event monitor.
```
 SET EVENT MONITOR MON_UTIL STATE=1
```

Query the logical data groups for information about utility operations executed on the database. For example, the following query lists the history of each utility invocation per member.

WITH UTIL_HIST(TIMESTAMP, UTIL_TYPE, ACTION, PHASE_TYPE, UTILITY_INVOCATION_ID,
      MEMBER, SQLCODE) AS
 (SELECT A.EVENT_TIMESTAMP,
        A.UTILITY_TYPE,
        CAST('START' AS VARCHAR(32)),
        CAST(NULL AS VARCHAR(16)),
        A.UTILITY_INVOCATION_ID,
        A.MEMBER,
        CAST(NULL as INTEGER)
 FROM UTIL_START AS A
 UNION ALL
  SELECT B.EVENT_TIMESTAMP,
          B.UTILITY_TYPE,
          CASE WHEN EVENT_TYPE IN ('UTILPHASESTART') THEN
             CAST('PHASE START' AS VARCHAR(32))
          ELSE
             CAST('PHASE STOP' AS VARCHAR(32))
          END CASE,
          CAST(UTILITY_PHASE_TYPE AS VARCHAR(16)),
          B.UTILITY_INVOCATION_ID,
          B.MEMBER,
          CAST(NULL as INTEGER)
  FROM UTIL_PHASES AS B
 UNION ALL
  SELECT C.EVENT_TIMESTAMP,
          C.UTILITY_TYPE,
          CAST('STOP' AS VARCHAR(32)),
          CAST(NULL AS VARCHAR(16)),
          C.UTILITY_INVOCATION_ID,
          C.MEMBER,
          C.SQLCODE
  FROM UTIL_STOP AS C)
SELECT * FROM UTIL_HIST
ORDER BY UTILITY_INVOCATION_ID, MEMBER, TIMESTAMP ASC

The resulting report of utility events can be used to:

Identify any utilities that are overlapping. For example, a utility starts on a partition before another utility has stopped on that same partition.
Determine where a utility is spending its time. For example, how much time was spent in each phase. Note: In 12.1 this is only available for the table space backup phase of online backups.

TIMESTAMP                  UTIL_TYPE ACTION      PHASE_TYPE    UTILITY_INVOCATION_ID MEMBER SQLCODE
-------------------------- --------- ----------  ------------- --------------------- ------ -------
2010-10-31-17.29.04.545210 LOAD      START       -             x'18A901F...621'      0      -
2010-10-31-17.50.04.344230 LOAD      STOP        -             x'18A901F...621'      0      0
2010-10-31-17.29.04.545211 LOAD      START       -             x'18A901F...633'      1      -
2010-10-31-17.50.04.344229 LOAD      STOP        -             x'18A901F...633'      1      0
2010-10-31-17.29.04.344210 BACKUP    START       -             x'18A901F...645'      0      -
2010-10-31-17.50.04.344211 BACKUP    PHASE START BACKUPTS      x'18A901F...645'      0      0
2010-10-31-17.51.04.545214 BACKUP    PHASE STOP  BACKUPTS      x'18A901F...645'      0      -
2010-10-31-17.52.04.344218 BACKUP    STOP        -             x'18A901F...645'      0      0
8 record(s) selected.