The EVMON_FORMAT_UE_TO_XML table function extracts binary events from an unformatted event table and formats them into an XML document.
>>-EVMON_FORMAT_UE_TO_XML--(--options--,------------------------> >--FOR EACH ROW OF--(--fullselect-statement--)--)--------------><
The schema is SYSPROC.
EXECUTE privilege on the EVMON_FORMAT_UE_TO_XML function.
SELECT privilege on the unformatted event table.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT evmon.* FROM TABLE (
EVMON_FORMAT_UE_TO_XML (
NULL,
FOR EACH ROW OF (
select * from MYLOCKS
order by EVENT_TIMESTAMP )))
AS evmon;
SELECT evmon.* FROM TABLE (
EVMON_FORMAT_UE_TO_XML (
NULL,
FOR EACH ROW OF (
select * from LOCK order by EVENT_TIMESTAMP
where EVENT_TYPE = 'LOCKWAIT'
and EVENT_TIMESTAMP >= CURRENT_TIMESTAMP - 5 hours )))
AS evmon;
SELECT evmon.* FROM TABLE (
EVMON_FORMAT_UE_TO_XML(
'LOG TO FILE',
FOR EACH ROW OF (
select * from UOW order by EVENT_TIMESTAMP
where WORKLOAD_NAME = 'PAYROLL'
and EVENT_TIMESTAMP = CURRENT_TIMESTAMP - 32 hours )))
AS evmon;
SELECT EVENT.UOW_ID, EVENT.APPLICATION_ID, EVENT.SESSION_AUTHID,
EVENT.START_TIME, EVENT.STOP_TIME
FROM TABLE(
EVMON_FORMAT_UE_TO_XML(
'LOG TO FILE',
FOR EACH ROW OF(
select * from UOWEVMON )))
AS UEXML,
XMLTABLE(
XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon' ),
'$uowevent/db2_uow_event'
PASSING XMLPARSE( DOCUMENT UEXML.XMLREPORT ) as "uowevent"
COLUMNS UOW_ID INTEGER PATH 'uow_id',
MEMBER SMALLINT PATH '@member',
APPLICATION_ID VARCHAR(128) PATH 'application_id',
SESSION_AUTHID VARCHAR(128) PATH 'session_authid',
START_TIME TIMESTAMP PATH 'start_time',
STOP_TIME TIMESTAMP PATH 'stop_time'
)
AS EVENT
Depending on the event monitor type that produced the UE table, the EVMON_FORMAT_UE_TO_XML table function might map multiple records from the unformatted event table into a single event. In such a case, the records are cached in memory until all the records that make up the event are received. A larger memory requirement might result if the records passed into the table function are not in the order they were created and inserted into the table. If the records are not sorted in this manner, the table function must cache records for multiple events. To avoid this issue, qualify the fullselect-statement parameter with an ORDER BY clause that contains the following columns: EVENT_ID, EVENT_TIMESTAMP, EVENT_TYPE, and MEMBER. Memory consumption is reduced because at any particular time, the table function is processing and caching records from only a single event.
There is not a one-to-one mapping between the records written to the UE table and the output of the EVMON_FORMAT_UE_TO_XML table function. Some events generate multiple records in the UE table; some result in just one record being added. The EVMON_FORMAT_UE_XML table function always combines all records from a UE table that describe a single event into one XML document.
If partial or incomplete events exist in the UE table, a message (SQL443N) is returned when you run EVMON_FORMAT_UE_TO_XML, whether or not you specify the LOG_PARTIAL_EVENTS option. You can suppress the message by using the SUPPRESS_PARTIAL_EVENTS_ERR option. Incomplete events can occur when an agent finishes processing before the entire event record can be inserted in to the UE table. This situation can sometimes arise where locking is involved, particularly in partitioned database environments. For example, when the LOCKWAIT threshold is exceeded, details about the holder of the lock are written to the UE table. However, details about agents waiting for a lock on the same object are not captured until the lock times out or the waiter acquires the lock. If EVMON_FORMAT_UE_TO_XML is run before the agent waiting for the lock has written its information, then only a part of the information about the lock might exist in the UE table.
When you specify the LOG_PARTIAL_EVENTS option, incomplete events in the UE table are written to a separate XML document. In addition, a message is written to the db2diag log files indicating that an incomplete event took place. The message specifies the file name of the XML document that contains details about the incomplete event. The XML documents produced can be formatted using the db2evmonfmt tool.
Column Name | Data Type | Description or corresponding monitor element |
---|---|---|
XMLID | VARCHAR(1024) | xmlid - XML ID monitor element |
XMLREPORT | BLOB(100M) | An XML document containing a single complete event. Each document has a maximum size of 100 MB. |