Example: Listing LOAD operations using the change history event monitor

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

Scenario

In this example, the database administrator (DBA) wants to capture and list the history of all load utility executions on a database. To track LOAD utility events.

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

 CREATE EVENT MONITOR MON_LOAD
    FOR CHANGE HISTORY WHERE EVENT IN (LOAD)
    WRITE TO TABLE
       CHANGESUMMARY (TABLE UTIL_COMMON),
       UTILSTART (TABLE LOAD_START),
       UTILSTOP (TABLE LOAD_STOP),
       UTILLOCATION (TABLE LOAD_INPUT_FILES),
       UTILPHASE (TABLE LOAD_PHASES);

Activate the event monitor.
```
 SET EVENT MONITOR MON_LOAD STATE=1
```

Query the logical data groups for information about LOAD operations executed on the database. For example, the following query lists the start and stop time of all executed load utilities. The query shows only the coordinator start and stop times. It ignores pause and resume records in order to show the full elapsed time of utility execution.

 SELECT A.APPL_ID,
        A.COORD_MEMBER,
        A.EVENT_TIMESTAMP AS START_TIME,
        B.EVENT_TIMESTAMP AS STOP_TIME,
        A.TABLE_SCHEMA,
        A.TABLE_NAME,
        SQLCODE,
        VARCHAR(A.UTILITY_DETAIL, 200) AS DETAIL
    FROM LOAD_START AS A
         LOAD_STOP AS B
         UTIL_COMMON AS C
    WHERE A.UTILITY_INVOCATION_ID = B.UTILITY_INVOCATION_ID AND
          A.UTILITY_START_TYPE = 'START' AND
          B.UTILITY_STOP_TYPE = 'STOP' AND
          A.MEMBER = B.MEMBER AND
          A.MEMBER = A.COORD_MEMBER
    ORDER BY A.EVENT_TIMESTAMP ASC

The result of the query shows that two load utilities were executed and provides details on their start and stop times, the target (table name) of the load, and details about the loads that were executed.

APPL_ID                  START_TIME                 STOP_TIME                 
------------------------ -------------------------- --------------------------
*LOCAL.test.110131213809 2010-10-31-17.29.04.545210 2010-10-31-17.29.04.545210
*LOCAL.test.110131213809 2010-10-31-17.29.04.545210 2010-10-31-17.29.04.545210

TABLES_SCHEMA TABLE_NAME SQLCODE DETAIL       
------------- ---------- ------- --------.....
TEST          T1               0 LOAD CURSOR..
TEST          T3               0 LOAD DEL...  

2 record(s) selected.