This tutorial was designed to introduce you to the set of monitoring tools available with DB2 10.1 and to show you how each is used to monitor how well (or how poorly) your database system is operating. Database monitoring is a vital activity that, when performed regularly, provides continuous feedback on the health of a database system. There are two distinct methods for monitoring operations in a database. The first involves monitoring table functions and administrative views, and allows you to view information that shows the state of various aspects of the database at a specific point in time. The second method utilizes event monitors, that can capture and log historical information as specific types of database events occur.
When invoked, monitoring table functions collect information from relevant DB2 monitoring elements that continuously accumulate data at the request, activity, and data object levels. Monitoring table functions employ the new lightweight monitoring infrastructure introduced in DB2 9.7, which was designed to avoid performance degradation effects on the database operation. Monitoring data and metrics can also be obtained by using an alternative set of SQL administrative views.
Event monitors provide a way to collect monitor data when events or activities occur that cannot be captured using monitoring table functions. Additionally, while monitoring elements begin collecting information once the database is activated in background processes, event monitors must be specifically created before they can be used.
Workload management allows you to attain a stable and predictable environment through automatic identification and management of work carried out on the data server. Monitoring of workload management is critical for validating the health and efficiency of the system. Monitoring table functions and event monitors are used to monitor workload management efficiency. Workload management monitoring table functions generally provide computed value data that is more statistical in nature. Workload management event monitors include the Activity, Threshold Violation, and Statistics event monitors.
The Explain facility allows you to capture and view detailed information about the access plan chosen for a particular SQL statement, as well as performance information that can be used to help identify poorly written statements or a weakness in database design. Specifically, explain data helps you understand how the DB2 Database Manager accesses tables and indices to satisfy a query. Explain data can also be used to evaluate any performance tuning action taken. Before explain information can be captured, a special set of explain tables must be created.
Visual Explain is a GUI tool that provides database administrators and application developers with the ability to view a graphical representation of the access plan chosen for a particular SQL statement. However, Visual Explain can only be used to view explain snapshot data; to view explain data collected and written to the explain tables, the db2exfmt tool must be used.
The db2ls and db2val utilities allow you to list installed DB2 products and features, and validate DB2 installed copies. System tools also include the db2mtrk utility, designed to provide a complete report of the memory status for instances, databases, and applications.
The db2pd utility is designed to retrieve information from appropriate
DB2 database system memory sets and produce a thorough report that can
be used to monitor or troubleshoot a database system (or any
component of a database system). The db2pd utility is invoked by