EVMON_FORMAT_UE_TO_XML table function - convert unformatted events to XML
The EVMON_FORMAT_UE_TO_XML table function extracts binary events from an unformatted event table and formats them into an XML document.
Syntax
The schema is SYSPROC.
Table function parameters
- options
- An input argument of type VARCHAR(1024) that represents a list
of keyword options supported by this table function.
- LOG_TO_FILE
- Indicates that the table
function is to write the XML document to a file if the XML document is greater than 100 MB. If the
XML document exceeds 100 MB, you can get the entire document by using the LOG_TO_FILE option. The
file is written to the <xml_document_id>.xml file, where
<xml_document_id>
is the unique ID generated for each document. The output file is written to the diagnostic path directory. - LOG_PARTIAL_EVENTS
- Indicates that the table function is to write all partial (incomplete) events to a file. See the Usage notes section of this topic for more information about partial events.
- SUPPRESS_PARTIAL_EVENTS_ERR
- Suppresses the SQL443N warning message when partial or incomplete events exist in the unformatted event (UE) table. For more information about partial events, see the Usage notes section.
- NULL
- No options selected.
- fullselect-statement
- The fullselect statement is a query that conforms to the rules
of the SELECT statement. The query must follow the following rules:
- The query must use the "*" clause or specify all the columns of the unformatted event table. Otherwise an error is returned. The columns must be specified in the same order as returned by the DESCRIBE statement of the unformatted event table.
- The query must select only from an unformatted event table.
- The WHERE clause can use any of the non-LOB columns of the unformatted event table to filter out events.
- The SELECT statement must be specified by the keyword FOR EACH ROWS OF, enclosed in brackets.
Authorization
EXECUTE privilege on the EVMON_FORMAT_UE_TO_XML function.
SELECT privilege on the unformatted event table.
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Examples
MYLOCKS.
SELECT evmon.* FROM TABLE (
EVMON_FORMAT_UE_TO_XML (
NULL,
FOR EACH ROW OF (
select * from MYLOCKS
order by EVENT_TIMESTAMP )))
AS evmon;
LOCKWAITthat have occurred in the last 5 hours from the unformatted event table
LOCK.
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;
PAYROLLthat occurred in the last 32 hours from the unformatted event table
UOW. Write the result to a file if any document is greater than 100 MB.
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;
UOWEVMONtable, and use the XMLTABLE table function to present the UOW ID, UOW start and stop times, and the user ID for the person who issued the unit of work.
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
Usage notes
- Impact of the EVMON_FORMAT_UE_TO_XML table function on memory usage
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.
- Relationship of records in a UE table to the output of the EVMON_FORMAT_UE_TO_XML table function
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.
- Partial events
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.
Information returned
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. |