Accessing event monitor data in regular tables

You can use SQL to directly access event monitor data that is written to regular relational tables.

Before you begin

Before accessing data, you must perform the following tasks:

Create and activate the event monitor
Enable data collection if required for the type of event monitor that you are using and the type of data that you want to collect
Run the workload or applications for which you want to collect monitoring data

Optionally, depending on how you are using the event monitor data, deactivate data collection before you start examining the event data. If the event monitor remains active, it continues to write data to the output tables. Therefore, the results from one query might differ from the results that you obtain by running the same query later on.

About this task

Accessing event monitor data from relational tables involves using SQL to formulate queries to retrieve data from the tables produced by the event monitor.

Procedure

To retrieve information from the tables that are produced by an event monitor that writes to tables:

Formulate a SELECT statement to display the monitor element data you want to see.
For example, to request lock data for the payroll workload from a locking event monitor named mylocks, you might use a query such as the following one:
```
SELECT DISTINCT CAST(STMT_TEXT AS VARCHAR(25)) STMT, LP.PARTICIPANT_NO, 
   VARCHAR(LP.APPL_NAME,10) APPL_NAME, LP.LOCK_MODE_REQUESTED,
     LP.PARTICIPANT_TYPE 
FROM LOCK_PARTICIPANT_ACTIVITIES_LOCK_MYLOCKS AS LPA 
JOIN LOCK_PARTICIPANTS_LOCK_MYLOCKS AS LP 
   ON LPA.EVENT_ID = LP.EVENT_ID  
   WHERE LP.WORKLOAD_NAME = 'PAYROLL'   
```
In this example, data from the LOCK_PARTICIPANTS table from the event monitor mylocks is joined with information from the LOCK_PARTICIPANTS_ACTIVITIES table to return the following results.
Run the SQL statement.

Results

STMT                      PARTICIPANT_NO APPL_NAME  LOCK_WAIT_VAL       
------------------------- -------------- ---------- --------------------
select * from staff                    2 db2bp                         0
select * from staff                    1 db2bp                      1000

LOCK_MODE_REQUESTED  PARTICIPANT_TYPE
-------------------- ----------------
                   0 OWNER           
                   1 REQUESTER       


  2 record(s) selected.