Summarizes DB2 event log generated by the db2evmon utility.

Command-Line Arguments

-rfn or --report-file-name

file name for the report

This argument is required.

-f or --files

list of log files to analyze

This argument is required.

Full command-line:

java info.mikaelsvensson.devtools.analysis.db2eventlog.Db2EventLogAnalyzer -cp [class path]
    --report-file-name <output report file path>
    --files <input log files>

About

The application will generate a report which will look something like this:

    Accumulated Execution Time
    --------------------------

       Samples       Time   Time Timing Details                                               Query Id Query String
       -------       ----   ---- --------------                                               -------- ------------
        214036    16992 s  80,6% Prepare: 12 s, Describe: 3 s, Open: 6 s, Close: 16970 s      17       select ...
       3565440     1378 s   6,5% Prepare: 143 s, Describe: 64 s, Open: 124 s, Close: 1046 s   8        select ...
        239528      375 s   1,8% Prepare: 11 s, Describe: 4 s, Open: 8 s, Close: 352 s        9        select ...
        203664      285 s   1,4% Prepare: 10 s, Describe: 3 s, Open: 7 s, Close: 265 s        11       select ...
        832180      241 s   1,1% Prepare: 30 s, Describe: 13 s, Open: 26 s, Close: 172 s      10       select ...
    ...
       -------       ----   ---- --------------                                               -------- ------------
       7377312    21086 s

    Accumulated execution time according to Elapsed Execution Time values: 21086 s
    Period of time when logging was enabled according to time stamps:      4212 s

    Number of Calls
    ---------------

       Samples       Time   Time Timing Details                                               Query Id Query String
       -------       ----   ---- --------------                                               -------- ------------
       3565440     1378 s   6,5% Prepare: 143 s, Describe: 64 s, Open: 124 s, Close: 1046 s   8        select ...
        832180      241 s   1,1% Prepare: 30 s, Describe: 13 s, Open: 26 s, Close: 172 s      10       select ...
        239528      375 s   1,8% Prepare: 11 s, Describe: 4 s, Open: 8 s, Close: 352 s        9        select ...
        232136      153 s   0,7% Prepare: 11 s, Describe: 4 s, Open: 8 s, Close: 131 s        15       select ...
        214036    16992 s  80,6% Prepare: 12 s, Describe: 3 s, Open: 6 s, Close: 16970 s      17       select ...
    ...
       -------       ----   ---- --------------                                               -------- ------------
       7377312    21086 s

    Accumulated execution time according to Elapsed Execution Time values: 21086 s
    Period of time when logging was enabled according to time stamps:      4212 s
    

Note the obvious problem with the recorded execution time: The accumulated execution time for the queries my greatly exceed the number of seconds between the earliest and latest recorded event. There may be several plausible reasons for this discrepancy, including rounding errors and concurrent query execution.

How Do I Make DB2 Record This Log?

Producing the necessary input to this report generator involves creating a monitor in the DB2 database, as per these instructions:

Start logging:

  1. Create event monitor:
    db2 "CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE '/tmp'"
  2. Turn on event monitor:
    db2 SET EVENT MONITOR stmon STATE 1

Stop logging:

  1. Turn off event monitor:
    db2 SET EVENT MONITOR stmon STATE 0
  2. Delete event monitor:
    db2 DROP EVENT MONITOR stmon
  3. Translate event monitor into readable stuff:
    db2evmon -path /tmp > /tmp/db2events.txt

The resulting db2events.txt is the input to the Db2EventLogReport utility.

Some notes:

  • Make sure you have got write permissions to log folder ("/tmp" in example above).
  • The log files generated by the event monitor are stored in a binary format and thus not readable to humans.
  • The db2evmon utility converts the binary log files to a very verbose text format. These text files can easily become several gigabytes in size since EACH query adds almost 2-3 KB to the text file (depending on the SQL querytext).

Notes

You may specify multiple input paths (i.e. log files). The utility will process them one by one.

Compiling

analysis$ mvn clean install dependency:copy-dependencies

Back to top

Reflow Maven skin by Andrius Velykis.