EVMON_FORMAT_UE_TO_XML table function - convert unformatted events to XML

Syntax

>>-EVMON_FORMAT_UE_TO_XML--(--options--,------------------------>

>--FOR EACH ROW OF--(--fullselect-statement--)--)--------------><

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. The maximum size of each document returned by this table function per row is 100 MB. 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 DB2® 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.

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.

Examples

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

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. 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 file 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