Using SQL Activity reports to monitor SQL performance

SQL Activity reports provide detailed information about individual SQL statements in a plan or package. The SQL Activity report aggregates threads by a combination of OMEGAMON® for Db2 PE identifiers. The SQL Activity trace shows details on a per thread basis.

At its simplest, the SQL Activity trace report shows each SQL statement in a thread by order of occurrence.

For each SQL statement basic information about the statement is shown. At the end of the thread, a summary is shown. If the thread originated at a different location, location information is also provided. A new thread is reported on a new page.

The end of the report shows a list of threads, in the order of occurrence, with a cross reference to the page where they are reported.

You can increase the level of detail by requesting workload. Workload shows information about DB2 activity associated with the execution of the statement. You can request workload details for:

  • All workload
  • Data capture
  • Exits
  • I/O activity
  • Locking activity for rows and pages
  • Scan activity
  • Sort activity
  • Host variables data

By default, no workload information is shown.

When you request workload, extra information is also provided:

Accounting
If IFCID 3 is included in your Db2 trace, OMEGAMON for DB2 PE includes an long Accounting trace for each thread.
Minibind
This shows bind information for each plan step. This is information taken from IFCID 22 and shows much of the information contained in the PLAN_TABLE.
User-defined functions
When user-defined functions are used, the report includes a Function Resolution block. This block shows information about the query, the path used, and detailed information about the function.

You can change the organization of data to group (summarize) events by:

  • Cursor
  • Program
  • Statement number
  • Statement type

Or you can change all of the above.

You can also sort the data by:

  • Time:
    • Elapsed
    • Exit
    • I/O
    • Suspension
    • TCB
  • Number of:
    • Exits
    • I/O requests
    • Merge passes for each sort
    • Pages scanned
    • Records sorted
    • Rows processed
    • Scans
    • Suspensions
    • Work files sorted