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 | Tivoli OMEGAMON® XE for Db2 Performance Expert on z/OS | |
| Db2 RUNSTATS utility | |||
| Db2 STOSPACE utility | |||
|
Dynamic Statement Cache |
|||
| 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.Related information:
- RUNSTATS utility
- Can report space use and access path statistics in the Db2 catalog.Related information:
- STOSPACE utility
- Provides information about the actual space allocated for storage groups, table spaces, table
space partitions, index spaces, and index space partitions.Related information:
- 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.Related information:
- -DISPLAY ACCEL (Db2)
- -DISPLAY ARCHIVE (Db2)
- -DISPLAY BUFFERPOOL (Db2)
- -DISPLAY DATABASE (Db2)
- -DISPLAY DDF (Db2)
- -DISPLAY FUNCTION SPECIFIC (Db2)
- -DISPLAY GROUP (Db2)
- -DISPLAY LOCATION (Db2)
- -DISPLAY LOG (Db2)
- -DISPLAY PROCEDURE (Db2)
- -DISPLAY PROFILE (Db2)
- -DISPLAY RLIMIT (Db2)
- -DISPLAY THREAD (Db2)
- -DISPLAY TRACE (Db2)
- -DISPLAY UTILITY (Db2)
- Catalog tables
- Help you determine when to reorganize table spaces and indexes.
- 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.
- 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.Related information:
- CICS Monitor Facility
- CICS monitoring collects data about the performance of all user- and CICS-supplied transactions during online processing for later offline analysis.Related information:
- Real-time statistics stored procedure (DSNACCOX)
- A sample stored procedure that makes recommendations to help you maintain your Db2 databases.Related information:
Other tools and facilities for Db2 performance monitoring
- IBM Data Studio
- In addition to providing other administrative functions, IBM Data Studio provides query serviceability and
tuning capabilities from workstation computers for queries that run in Db2 for z/OS
environments.Related information:
- IBM Data Server Manager
- With IBM Data Server
Manager, you can identify and
analyze database problems by reviewing real-time and historical performance metrics. Tuning advisors
provide expert recommendations about statistics, indexes, and performance
improvement.Related information:
- Db2 Connect
- Can monitor and report Db2
server-elapsed time for client applications that access Db2 data.Related information:
- 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.Related information:
- 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.
- System Management Facility (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.Related information:
- 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 Tivoli OMEGAMON XE 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 Tivoli OMEGAMON XE 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 Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS functions to provide reports for Db2 and QMF historical data.
Related information: - 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.Related information:
- Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS
- A
performance monitoring tool that formats performance data. Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS combines information from EXPLAIN and from
the Db2 catalog. You can use Tivoli OMEGAMON XE 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.
Related information:- 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:
- Tivoli Tivoli OMEGAMON XE 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 Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS .
Related information: - Db2 Query Workload Tuner for z/OS
- A full-featured SQL analysis and tuning tool, which enhances the functions provided by IBM Data Studio 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.
- IMS Fast Path Log Analysis utility (DBFULTA0)
- An IMS utility that provides performance reports for IMS Fast Path transactions.Related information:
- 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.Related information:
- 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.Related information: