db2evmonfmt tool for reading event monitor data

The Java™-based, generic XML parser tool, db2evmonfmt, produces a readable flat-text output (text version) or a formatted XML output from the data generated by an event monitor that writes its output to a regular table or that uses the unformatted event table. Based on the parameters that you specify, the db2evmonfmt tool determines how to parse the event monitor data and the type of output to create.

The db2evmonfmt tool is provided as Java source code. You must setup and compile this tool, before you can use it, by performing the following steps:

Locate the source code in the sqllib/samples/java/jdbc directory
Follow the instructions embedded in the Java source file to setup and compile the tool

You can modify the source code to change the output to your liking.

The tool uses XSLT style sheets to transform the event data into formatted text. You do not need to understand these style sheets. The tool will automatically load the correct style sheet, based on the event monitor type, and transform the event data. Each event monitor will provide default style sheets within the sqllib/samples/xml/data directory. The tool will also provide the following filtering options:

Event ID
Event timestamp
Event type
Workload name
Service class name
Application name

Tool syntax

connect

XML file

Tool parameters

java: To run the db2evmonfmt Java-based tool successfully, the java keyword must precede the tool name. The proper Java version to successfully run this tool is installed from the sqllib/java/jdk64 directory during product installation.

-d db_name: Specifies the database name to which a connection is made

-ue table_name: Specifies the name of the unformatted event table

-wtt write_to_table_event_monitor_name: Specifies the name of an event monitor with a WRITE TO TABLE target. Only unit of work (UOW), package cache, locking and change history event monitors are supported.

-u user_id: Specifies the user ID

-p password: Specifies the password

-f xml_filename: Specifies the name of the input XML file to format

-fxml: Produces a formatted XML document (pipe to stdout)

-ftext: Formats an XML document to a text document (pipe to stdout)

-ss stylesheet_name: Specifies the XSLT style sheet to use to transform the XML document

-id event_id: Displays all events matching the specified event ID. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

-type event_type: Displays all events matching the specified event type. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

-hours num_hours: Displays all events that have occurred within the specified last number of hours. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

-w workload_name: Displays all events that are part of the specified workload. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

-a appl_name: Displays all events that are part of the specified application. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

-s srvc_subclass_name: Displays all events that are part of the specified service subclass. This option is only valid when the -ue option has been specified; it will be ignored otherwise.

XSLT style sheets

The database manager provides default XSLT style sheets (see Table 1) which can be found in the sqllib/samples/java/jdbc directory. You can change these style sheets to produce the required output.

Table 1. Default XSLT style sheets for event monitors
Event monitor	Default XSLT style sheet
Locking	DB2EvmonLocking.xsl
Unit of work	DB2EvmonUOW.xsl
Package cache	DB2EvmonPkgCache.xsl

You can create your own XSLT style sheet to transform XML documents. You can pass these style sheets into the Java-based tool using the -ss stylesheet_name option.

Examples

Example 1

To obtain a formatted text output for all events that have occurred in the last 32 hours from the package cache unformatted event table PKG in database SAMPLE, issue the following command:

java db2evmonfmt -d sample -ue pkg -ftext -hours 32

Example 2

To obtain a formatted text output for all events of type LOCKTIMEOUT that have occurred in the last 24 hours from unformatted event table LOCK in database SAMPLE, issue the following command:

java db2evmonfmt -d sample -ue LOCK -ftext -hours 24 -type locktimeout

Example 3

To obtain a formatted text output from the XML source file LOCK.XML, extracting all events that match the event type LOCKWAIT in the last 5 hours, issue the following command:

java db2evmonfmt -f lock.xml -ftext -type lockwait -hours 5

Example 4

To obtain a formatted text output using the created XSLT style sheet SUMMARY.XSL for all events in the unformatted event table UOW in database SAMPLE, issue the following command:

java db2evmonfmt -d sample -ue uow -ftext -ss summary.xsl

Sample formatted flat-text output

The following sample of formatted flat-text output was generated from the locking event monitor XSLT style sheet:

--------------------------------------------------
Event Entry     : 0
Event ID        : 1
Event Type      : Locktimeout
Event Timestamp : 2008-05-23-12.00.14.132329000
--------------------------------------------------

Lock Details
------------
Lock Name       : 02000401000000000000000054
Lock Type       : Table
Lock Attributes : 00000000
Lock Count      : 1
Lock Hold Count : 0
Lock rrIID      : 0
Lock Status     : Waiting
Cursor Bitmap   : 00000000
Tablespace Name : USERSPACE1
Table Name      : NEWTON  .SARAH

Attributes           Requestor                      Holder                        
----------           ---------                      ------                        
Application Handle   [0-35]                         [0-16]                        
Application ID       *LOCAL.horton.080523160016    *LOCAL.horton.080523155938   
Application Name     xaplus0001                     db2bp                         
Authentication ID    NEWTON                         HORTON                        
Requesting Agent     65                             21                            
Coordinating Agent   65                             21                            
Application Status   SQLM_CONNECTPEND               SQLM_CONNECTPEND              
Lock Timeout         5000                           0                             
Workload Name        XAPLUS0010_WL02                SYSDEFAULTUSERWORKLOAD        
Service Subclass     XAPLUS0010_SC02                SYSDEFAULTSUBCLASS            
Current Request      Execute                        Execute Immediate             
Lock Mode            Intent Exclusive               Exclusive                     
tpmon Userid                                                                      
tpmon Wkstn                                                                       
tpmon App                                                                         
tpmon Accstring                                                                   

Lock Requestor Current Activities
---------------------------------
Activity ID     : 2
Uow ID          : 1
Package ID      : 65426E4D4B584659
Package SectNo  : 3
Package Name    : NEWTON  
Package Schema  : AKINTERF
Package Version : 
Reopt           : always
Eff Isolation   : Cursor Stability
Eff Locktimeout : 5
Eff Degree      : 0
Nesting Level   : 0
Stmt Unicode    : No
Stmt Flag       : Dynamic
Stmt Type       : DML, Insert/Update/Delete
Stmt Text       : INSERT INTO SARAH VALUES(:H00008, :H00013, :H00014)


Lock Requestor Past Activities
------------------------------
Activity ID     : 1
Uow ID          : 1
Package ID      : 65426E4D4B584659
Package SectNo  : 2
Package Name    : NEWTON  
Package Schema  : AKINTERF
Package Version : 
Reopt           : always
Eff Isolation   : Cursor Stability
Eff Locktimeout : 5
Eff Degree      : 0
Nesting Level   : 0
Stmt Unicode    : No
Stmt Flag       : Dynamic
Stmt Type       : DML, Insert/Update/Delete
Stmt Text       : INSERT INTO NADIA VALUES(:H00007)


Lock Holder Current Activities
------------------------------

Lock Holder Past Activities
------------------------------
Activity ID     : 1
Uow ID          : 2
Package ID      : 41414141414E4758
Package SectNo  : 201
Package Name    : NULLID  
Package Schema  : SQLC2G13
Package Version : 
Reopt           : none
Eff Isolation   : Cursor Stability
Eff Locktimeout : 5
Eff Degree      : 0
Nesting Level   : 0
Stmt Unicode    : No
Stmt Flag       : Dynamic
Stmt Type       : DML, Select (blockable)
Stmt Text       : select * from newton.sarah

Activity ID     : 2
Uow ID          : 2
Package ID      : 41414141414E4758
Package SectNo  : 203
Package Name    : NULLID  
Package Schema  : SQLC2G13
Package Version : 
Reopt           : none
Eff Isolation   : Cursor Stability
Eff Locktimeout : 5
Eff Degree      : 0
Nesting Level   : 0
Stmt Unicode    : No
Stmt Flag       : Dynamic
Stmt Type       : DML, Lock Table
Stmt Text       : lock table newton.sarah in exclusive mode


--------------------------------------------------
Event Entry     : 1
Event ID        : 2
Event Type      : Locktimeout
Event Timestamp : 2008-05-23-12.04.42.144896000
--------------------------------------------------

...
...
...

Usage notes

The db2evmonfmt utility is a Java-based tool which must be preceded by the java keyword in order to run successfully. The Java version required is that which is installed with the product from the sqllib/java/jdk64 directory.

If connect parameters are supplied, the db2evmonfmt tool retrieves data directly from an event monitor. When the db2evmonfmt tool retrieves data directly from an event monitor, the command calls the EVMON_FORMAT_UE_TO_XML table function to build an XML document from the binary events in the event monitor. If you want details about partial events, you must modify the EVMON_FORMAT_UE_TO_XML call in the java-based tool to add the LOG_PARTIAL_EVENTS option. After you modify the EVMON_FORMAT_UE_TO_XML call, you must recompile the tool. For more information about partial events and the LOG_PARTIAL_EVENTS option, see EVMON_FORMAT_UE_TO_XML table function - convert unformatted events to XML.

Note: You can also use the EVMON_FORMAT_UE_TO_XML table function directly to format the binary events, contained in the unformatted event table BLOB column, into an XML document.