Creating and viewing SQL activity traces

If you have MONITOR1 and MONITOR2 privileges, you can create traces on the DB2® system-wide SQL activity that takes place during the processing of a DB2 application. You can create such traces only if you are not viewing history data.

The trace results are displayed in a Web browser window. To ensure correct display, use the latest version of a web browser.

You can select a preferred browser go to Monitor > Configuration, and specify a default HTML browser in the Preferences window.

A trace can only be created if:
  • The Performance Warehouse server is running.
  • A Performance Warehouse system has been associated with the monitored DB2 subsystem. Verify that the Performance Warehouse page of the DB2 System Properties window contains the alias of the Performance Warehouse database maintained by the OMEGAMON® Collector that is monitoring the DB2 subsystem. This alias is equal to the database alias that you specified when cataloging the DB2PM database by using the Configuration Assistant of DB2. For information about how to display the DB2 System Properties window, refer to Defining a Db2 subsystem.
To create a trace, start in the Thread Summary or Thread Details window. When you select a thread, all threads with the same LUWID as the selected one, are included in the trace.
  1. Do one of the following:
    • In the Thread Summary window, select the thread for which you want to create the trace and then click Selected > SQL Activity Tracing.
    • In the Thread Details window, click Thread Details > SQL Activity Tracing.

    The SQL Activity Report Generation window is displayed.

    Here, you can specify the criteria for the data collection to stop and the SQL activity events you want to see in the trace report.

  2. In the Elapsed time and Number of records collected fields, you can leave the default specifications or specify both or only one of the criteria.

    If you specify both criteria, data collection stops as soon as the first criterion is met. For example, if you specify an elapsed time of five minutes (00:05:00) and a number of records of 5 and this number is reached after a minute, the trace stops and disregards the remaining four minutes.

  3. Optionally, select the SQL statements with a negative return code and the workload blocks that are shown in trace report.
  4. Click OK.

    If the message Data set not cataloged is displayed, set the OMEGAMON Collector startup parameter USERAUTHORIZATION to YES.

    For more information about how to set this parameter, refer to Configuration and Customization.

The collection of the performance data and the generation of the SQL activity trace are started and the SQL Activity Tracing window is displayed.

When the trace report is complete and the data sets are downloaded, the results are shown in a Web browser window.

The SQL activity trace results are downloaded as temporary files and are deleted when you leave OMEGAMON for Db2 Performance Expert. To save these files, use the save functions provided by your Web browser.