Accessing event information written by an activity event monitor to a table

Activity event monitors can write their output to tables, files, and pipes. For more information about using data written to files and pipes, see Event monitor self-describing data stream.

Before you begin

You must have created and activated an activity event monitor and enabled data collection.

About this task

The tables that an activity event monitor produces are described in Target tables, control tables, and event monitor table management.

Procedure

To access the data produced by an activity event monitor:

Formulate a query that returns the columns that you want to see.
For example, if you are interested in information about the text of the statements that are associated with a specific unit of work, you might formulate a query such as the one that follows:
```
SELECT UOW_ID, SUBSTR(STMT_TEXT, 1,70) AS STMT_TEXT FROM ACTIVITYSTMT_ACTEVMON 
  WHERE UOW_ID=11
```
In this case, the event monitor is named actevmon.

Run the query.

The preceding query might return results similar to the ones that follow:

UOW_ID    STMT_TEXT
--------- ---------------------------------------------------------------------
       11 select * from gosaleshr.employee_expense_detail order by expense_date

  1 record(s) selected.

Results

Example

If you want to access the data in the DETAILS_XML column of the ACTIVITY table, you can use any of the interfaces that are provided for this purpose. For example, to see metrics information that an activity event monitor collects for a unit of work, you might use a statement such as the one that follows:

SELECT  SUBSTR(B.METRIC_NAME, 1, 20) METRIC_NAME, B.VALUE  
   FROM ACTIVITY_ACTEVMON AS A,
   TABLE(MON_FORMAT_XML_METRICS_BY_ROW(A.DETAILS_XML))AS B 
   WHERE UOW_ID=23   
   ORDER BY B.VALUE DESC

This statement returns all of the activity metrics collected for the unit of work with the UOW_ID of 23:

METRIC_NAME          VALUE
-------------------- --------------------
TOTAL_CPU_TIME                     140625
ROWS_READ                             977
TOTAL_ACT_TIME                        880
STMT_EXEC_TIME                        880
COORD_STMT_EXEC_TIME                  880
TOTAL_SECTION_PROC_T                  880
TOTAL_SECTION_TIME                    880
        ⋮                               ⋮
FCM_TQ_SEND_WAITS_TO                    0
FCM_MESSAGE_SEND_WAI                    0
FCM_SEND_WAITS_TOTAL                    0
FCM_RECV_WAITS_TOTAL                    0

  92 record(s) selected.

For more information about working with XML data returned by event monitors, see Interfaces that return monitor data in XML documents.