Reports generated using the MONREPORT module

The MONREPORT module generates text reports of monitoring data that you can use to troubleshoot SQL performance problems.

You can generate the following reports using the MONREPORT module:

Table 1. List of reports generated using the MONREPORT module
Report Name	Procedure to create report	Main data source / table functions
Summary report	MONREPORT.DBSUMMARY	MON_GET_SERVICE_SUBCLASS and selected details from MON_GET_CONNECTION and MON_GET_WORKLOAD
Connection report	MONREPORT.CONNECTION	MON_GET_CONNECTION
Current Applications report	MONREPORT.CURRENTAPPS	Includes fields from MON_GET_CONNECTION, MON_GET_UNIT_OF_WORK, WLM_GET_SERVICE_CLASS_AGENTS, WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
Current SQL report	MONREPORT.CURRENTSQL	MON_GET_PKG_CACHE_STMT (For the executable_id obtained from the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function.)
Package Cache report	MONREPORT.PKGCACHE	MON_GET_PKG_CACHE_STMT
Current Lock Wait report	MONREPORT.LOCKWAIT	Most data from MON_GET_APPL_LOCKWAIT; additional data from MON_GET_CONNECTION, WLM_GET_SERVICE_CLASS_AGENTS, WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES, MON_GET_PKG_CACHE_STMT, MON_GET_TABLE

Most reports start with a summary section that provides one line of key information for each item in the report. For example, the Connection report contains a one-line summary of each connection. The main body of the report consists of a detailed section for each item in the summary.

Each metric in the report is labeled with the underlying monitor element name (for example: CLIENT_IDLE_WAIT_TIME = 44). To determine what the metric represents, search the Information Center for the monitor element name.

Reports for initial diagnosis

An important use of these reports is to troubleshoot SQL performance slowdowns. Each report is designed to answer certain diagnosis questions. Some reports support initial diagnosis, while others support subsequent detailed diagnosis of particular types of problems.

Initial diagnosis involves:

Determining the problem category, by narrowing the problem down to the aspect or stage of processing that has slowed down.
Identifying the SQL statements involved in the problem and collecting information about the SQL statements for further analysis.

Table 2. MONREPORT module reports suitable for initial diagnosis
Procedure name	Information provided and usage
MONREPORT.DBSUMMARY Part 1: System Performance	Part 1 of the Summary report provides monitor data for most aspects of processing aggregated across the entire database. This information is useful for answering questions about the aspect or stage of processing that has slowed down. For example: Is the problem inside or outside the data server? Is there a computing resource bottleneck? Are requests in a wait state? If so, for what resource? Is the slowdown located in a particular data server processing component?
MONREPORT.DBSUMMARY Part 2: Application Performance	Part 2 of the Summary report provides key performance indicators for each connection, workload, and service class. This information is useful for answering questions about the scope of application requests involved in the slowdown. For example: Is this slowdown a general system slowdown that affects much or all the workload? Is this slowdown limited to SQL statements issued from a particular source such as particular connections, database workloads or database service classes?
MONREPORT.DBSUMMARY Part 3: Member level information	Part 3 of the Summary report provides key performance indicators for each member. This information is useful for determining whether the slowdown is isolated to one or a few members.
MONREPORT.CURRENTSQL	The current SQL report provides information about statements that are currently running, in the form of several lists of the top N activities. The statements are ranked by different metrics: processing resource, rows processed, direct reads and direct writes. This information is useful for determining whether the slowdown is isolated to one or a few SQL statements. If the slowdown is isolated to one or a few SQL statements, those statements are likely to appear in this report of top statements.
MONREPORT.PKGCACHE	The package cache report provides information about statements that have run recently and are stored in the package cache. This report shows several summaries, each listing the top N activities. The activities are ranked by the following monitor elements: CPU wait time rows processed num_coord_exec_with_metrics - Number of executions by coordinator agent with metrics monitor element (if no member was specified) or num_exec_with_metrics - Number of executions with metrics collected monitor element (if a member was specified) I/O wait time This report contains a summary for each of these metrics as well as a report for each execution. This information is useful for determining whether the slowdown is isolated to one or a few SQL statements. If so, those statements are likely to appear at the top in this report. The information per execution can help identify the most costly statements while the information summed across executions can help identify statements with the most impact on the system cumulatively considering both the statement cost and frequency of execution.
MONREPORT.CURRENTAPPS	The current applications report show the current processing state for units of work, agents, and activities. The report starts with a summary section showing the number of current connections and activities, as well as a series of summaries, such as the summary of current units of work by workload occurrence state. The body of the report consists of one section for each connection that provides the details of the connection. This information is useful for viewing all the work currently running on the system. This allows you to check for patterns that might identify the problem category.

Reports for detailed diagnosis

After completing the initial diagnosis, you might need to pursue a specialized or detailed set of troubleshooting analyses for the problem category you identified during the initial diagnosis phase.

Table 3. MONREPORT module reports suitable for detailed diagnosis
Procedure name	Information provided and usage
MONREPORT.CONNECTION	If the MONREPORT.DBSUMMARY report showed that the slowdown is limited to SQL statements issued from a particular connection, then you can view detailed information about the affected connection. This report contains the same metrics as Part 1 of the MONREPORT.DBSUMMARY report, but it presents this information for each connection.
MONREPORT.LOCKWAIT	If the reports viewed during the initial diagnosis suggest there is a lock wait problem, then you can view detailed information about each lock wait currently in progress. This information includes lock holder and lock requester details, as well as characteristics of the lock held and the lock requested.