Monitoring your queries using the Database Monitor

Start Database Monitor (STRDBMON) command gathers information about a query in real time and stores this information in an output table. This information can help you determine whether your system and your queries are performing well, or whether they need fine-tuning. Database monitors can generate significant CPU and disk storage overhead when in use.

You can gather performance information for a specific query, for every query on the system, or for a group of queries on the system. When a job is monitored by multiple monitors, each monitor is logging rows to a different output table. You can identify rows in the output database table by its unique identification number.

When you start a monitor using the Start Database Monitor (STRDBMON) command, the monitor is automatically registered with System i® Navigator and appears in the System i Navigator monitor list.

Note: Database monitors also contain SQL statement text and variable values. If the variable values or SQL statements contain sensitive data you should create database monitors in a library that is not publicly authorized to prevent exposure to the sensitive data.

What kinds of statistics you can gather

The database monitor provides the same information that is provided with the query optimizer debug messages (Start Debug (STRDBG)) and the Print SQL information (PRTSQLINF) command. The following is a sampling of the additional information that is gathered by the database monitors:

System and job name
SQL statement and subselect number
Start and end timestamp
Estimated processing time
Total rows in table queried
Number of rows selected
Estimated number of rows selected
Estimated number of joined rows
Key columns for advised index
Total optimization time
Join type and method
ODP implementation

How you can use performance statistics

You can use these performance statistics to generate various reports. For instance, you can include reports that show queries that:

Use an abundance of the system resources.
Take a long time to execute.
Did not run because of the query governor time limit.
Create a temporary index during execution
Use the query sort during execution
Might perform faster with the creation of a keyed logical file containing keys suggested by the query optimizer.

Note: A query that is canceled by an end request generally does not generate a full set of performance statistics. However, it does contain all the information about how a query was optimized, except for runtime or multi-step query information.