Facilities and tools for Db2 performance monitoring
You can use various tools and facilities to monitor Db2 activity and performance.
| Data source | Data Writer (if applicable) | Reporting Tool (if applicable) | Reduction and History |
|---|---|---|---|
| CICS® monitoring facility | SMF or CICS journal | Tivoli® Decision Support for z/OS® | Tivoli Decision Support for z/OS |
| IMS monitor | |||
| IMS log records | IMS log | IMS Performance Analyzer for z/OS | |
| Db2 trace | SMF or GTF | IBM® OMEGAMON® for Db2 Performance Expert on z/OS | |
| Db2 RUNSTATS utility | |||
| Db2 STOSPACE utility | |||
|
Dynamic Statement Cache |
Db2 Administration Foundation |
||
| Db2 DISPLAY commands | Message output | ||
| Db2 catalog tables | |||
| RMF monitor | SMF | RMF reports |
Db2 for z/OS facilities for performance monitoring
- Db2 trace
- Provides Db2 accounting, statistics, auditing, and performance trace information. For more information, see:
- RUNSTATS utility
- Can report space use and access path statistics in the Db2 catalog. For more information, see:
- STOSPACE utility
- Provides information about the actual space allocated for storage groups, table spaces, table space partitions, index spaces, and index space partitions. For more information, see STOSPACE.
- DISPLAY commands
- Provide information about the status of things such as threads, databases, buffer pools, traces, allied subsystems, applications, and the allocation of tape units for the archive read process. For more information, see the topics for the various DISPLAY commands in Db2 commands .
- Catalog tables
- Help you determine when to reorganize table spaces and indexes. For more information, see:
- EXPLAIN
- Provides information about the access paths used by Db2. By using Db2 EXPLAIN you can capture and analyze information about plans, packages, or SQL statements when they are bound. The output appears in a supplied user table called plan table named PLAN_TABLE.
Additional EXPLAIN tables provide other kinds of information. For example, the statement table, named DSN_STATEMNT_TABLE, provides information about the estimated costs of executing SQL SELECT, INSERT, UPDATE, or DELETE statements. Similarly, the function table, named DSN_FUNCTION_TABLE contains information about user-defined functions that are called by a statement.
Many additional EXPLAIN tables exist to provide different types of information about the execution of SQL statements, and to enable the functions of certain optimization tools.
For more information, see:
- CICS journal
- Facilities for creating and managing journals during CICS processing. Journals can contain any data the user needs to facilitate subsequent reconstruction of events or data changes.
- CICS Attachment Facility statistics
- Provide information about the use of CICS threads. This information can be displayed on a terminal or printed in a report. For more information, see:
- CICS Monitor Facility
- CICS monitoring collects data about the performance of all user- and CICS-supplied transactions during online processing for later offline analysis. For more information, see .CICS Monitor Facility
- Real-time statistics stored procedure (DSNACCOX)
- A sample stored procedure that makes recommendations to help you maintain your Db2 databases. For more information, see DSNACCOX stored procedure.
Other tools and facilities for Db2 performance monitoring
- Db2 Connect
- Can monitor and report Db2 server-elapsed time for client applications that access Db2 data. For more information, see Reporting server-elapsed time.
- z/OS generalized trace facility (GTF)
- A z/OS service aid that can be used to collect Db2 trace data for performance analysis. GTF can also be used to analyze seek times and Supervisor Call instruction (SVC) usage, and for other services. For more information, see .Recording GTF trace data
- z/OS Resource Measurement Facility (RMF)
- An optional feature of z/OS that provides system-wide information on processor utilization, I/O activity, storage, and paging. RMF provides for three basic types of sessions: Monitor I, Monitor II, and Monitor III. Monitor I and Monitor II sessions collect and report data primarily about specific system activities. Monitor III sessions collect and report data about overall system activity in terms of work flow and delay. For more information, see:
- z/OS System Management Facilities (SMF)
- A z/OS service aid used to collect information from various z/OS subsystems. SMF can be used to collect Db2 accounting, statistics, audit and performance trace data. For more information, see:
- Tivoli Decision Support for z/OS
- A licensed program that collects SMF data into a Db2 database and allows you to create reports on the data.
Tivoli Decision Support for z/OS uses data from different sources, including SMF, the IMS log, the CICS journal, RMF, and Db2.
The data collection and reporting are based on user specifications. Therefore, experienced users can produce more useful reports than the predefined reports that are produced by other tools. Tivoli Decision Support also provides historical performance data that you can use to compare a current situation with previous data. Tivoli Decision Support is most useful for Db2 statistics and accounting records.
The following considerations apply to the use of Tivoli Decision Support for Db2 performance trace data:
- Because of the large number of different performance trace records, substantial effort is required to define the formats in Tivoli Decision Support. Changes in the records require review of the definitions.
- Tivoli Decision Support does not handle information from paired records, such as
start event
andend event.
These record pairs are used by IBM OMEGAMON for Db2 Performance Expert on z/OS to calculate elapsed times, such as the elapsed time of I/Os and lock suspensions.
Therefore, the recommendation is to use Tivoli Decision Support only for accounting and statistics records in most cases, and to use IBM OMEGAMON for Db2 Performance Expert on z/OS for Db2 performance trace data. If you do not use Tivoli decision support at all, you can create user applications to extend IBM OMEGAMON for Db2 Performance Expert on z/OS functions to provide reports for Db2 and QMF historical data.
For more information, see Tivoli Decision Support for z/OS.
- Tivoli OMEGAMON CICS Monitoring Facility (CMF)
- Provides performance information about each CICS transaction executed. It can be used to investigate the resources used and the time spent processing transactions. Be aware that overhead is significant when CMF is used to gather performance information. For more information, see CICS Monitor Facility.
- IBM OMEGAMON for Db2 Performance Expert on z/OS
- A performance monitoring tool that formats performance data. IBM OMEGAMON for Db2 Performance Expert on z/OS combines information from EXPLAIN and from the Db2 catalog. You can use IBM OMEGAMON for Db2 Performance Expert on z/OS for the following activities:
- Analysis of Db2 trace records: It displays information about the following objects and structures:
- Access paths
- Host variable definitions
- Indexes
- Join sequences
- Lock types
- Ordering
- Plans
- Packages
- Tables
- Table spaces
- Table access sequences
- Reporting: The batch report sets present the data you select in comprehensive reports or graphs containing system-wide and application-related information for both single Db2 subsystems and Db2 members of a data sharing group. You can combine instrumentation data from several different Db2 locations into one report. Batch reports can be used to examine performance problems and trends over a period of time.
- Monitoring: The online monitor provides a current
snapshot
view of a running Db2 subsystem, including applications that are running. The history function displays information about subsystem and application activity in the recent past. - Warehousing Db2 performance data. The performance warehouse function enables the following activities:
- Saving Db2 trace and report data in a performance database for further investigation and trend analysis.
- Configuring and scheduling the report and load processes from the workstation interface.
- Defining and applying analysis functions to identify performance bottlenecks.
- Buffer pool analysis: The buffer pool analyzer function, which can help you to optimize buffer pool usage. When viewing the reports, you can:
- Order by various identifiers such as buffer pool, plan, object, and primary authorization ID
- Sort by getpage, sequential prefetch, and synchronous read
- Filter the reports
You can also simulate buffer pool usage for varying buffer pool sizes and analyze the results of the simulation reports to determine the impact of any changes before making those changes to your current system.
- End-to-end SQL monitoring: The Extended Insight feature tracks and measures the flow of a single SQL request through all its application components, from the database engine to the end-user. This feature is particularly useful for monitoring SQL performance in distributed environments.
For more information, see:
- Tivoli OMEGAMON XE for Db2 Performance Monitor on z/OS
- Online monitoring and reporting (Db2 Performance Monitor on z/OS)
- Monitoring and problem determination (Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS )
- White Paper: OMEGAMON Extended Insight Analysis: Where is your application spending its time? (PDF)
- End-to-End SQL Monitoring Workspace (Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS )
- Quick Start Guide for the end-to-end SQL monitoring function (Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS )
- DB2 subsystem configuration, PE Client, and end-to-end SQL monitoring (Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS )
- Analysis of Db2 trace records: It displays information about the following objects and structures:
- IBM OMEGAMON for Db2 Performance Expert on z/OS XE for Db2 Performance Monitor
- Provides performance monitoring, reporting, and performance warehouse functions.
Db2 Performance Monitor on z/OS performs a subset of the complete functions of IBM OMEGAMON for Db2 Performance Expert on z/OS . For more information, see Monitoring and problem determination (Db2 Performance Monitor on z/OS).
- Db2 Query Workload Tuner for z/OS
- A full-featured SQL analysis and tuning tool, which enhances the functions provided by basic query tuning tools with additional capabilities and expert advisors. It also enables you to monitor and tune the performance of SQL queries and SQL query workloads.
- IMS log records
- Information about various IMS processes, including comprehensive information about transit times (actual system performance time), and IMS resource usage and availability. For more information, see:
- IMS Fast Path Log Analysis utility (DBFULTA0)
- An IMS utility that provides performance reports for IMS Fast Path transactions. For more information, see Fast Path Log Analysis utility (DBFULTA0).
- IMS Monitor
- The IMS Monitor collects data while the online IMS subsystem is running. It gathers information for all dispatch events and places it, in the form of IMS Monitor records, in a sequential data set. For more information, see IMS Monitor.
- IMS Performance Analyzer for Db2 for z/OS
- A performance analysis and tuning aid for Information Management System Database (IMS DB) and Transaction Manager (IMS TM) systems. For more information, see IMS Performance analyzer.