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 IBM® i Access Client Solutions (ACS) and appears in the ACS monitor list.
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.