Gather statistics about your queries with the database monitor

Database monitor statistics provide the most complete information about a query. You can gather performance statistics for a specific query or for every query on the system. There are several different ways to gather the statistics:

• Use the Start Database Monitor (STRDBMON) command and the End Database Monitor (ENDDBMON) command.
• Use the Start Performance Monitor (STRPFRMON) command with the STRDBMON parameter.
• Use memory resident database monitor APIs. See Gather statistics about your queries with memory resident database monitor APIs.

You can monitor a specific job or all jobs on the system. The statistics gathered are placed in the output database table specified on the command. Each job in the system can be monitored concurrently by two monitors:

• One started specifically on that job
• One started for all jobs in the system

When a job is monitored by two monitors, each monitor is logging rows to a different output table. You can identify rows in the output database table by each row's unique identification number.

What kinds of statistics you can gather

STRDBMON provides the same information that is provided with the query optimizer debug messages (STRDBG) and the Print SQL information (PRTSQLINF) command, along with the following information:

• System and job name
• SQL statement and sub-select 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 an extremely 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
• Could perform faster with the creation of a keyed logical file containing keys suggested by the query optimizer.