This section includes the following topics:
- About Precise for DB2
- Components of Precise for DB2
- How Precise for DB2 works
- What you can do with Precise for DB2
- Where to get more information
About Precise for DB2
Addressing the needs of DBAs, system administrators, managers, quality assurance specialists and planners, Precise for DB2 (Precise for DB2) provides a complete view of database performance by capturing, measuring, and correlating performance metrics from all critical system components.
Through its wide range of data presentations, Precise for DB2 provides the tools necessary for proactive monitoring, diagnosing, detecting, and correcting of performance problems before they become visible in your DB2 databases and related applications.
Current operational data is displayed to enable easy identification of problematic conditions. Recent operational data, pertaining to designated time periods, enables operations staff to detect problematic conditions that occurred in the past and that are likely to reoccur if not corrected. Performance data is archived and a fully detailed display of the historical data is provided for use in performance, QA, and capacity planning analysis.
Improved performance management
Precise for DB2 can help IT staff discover the answer to questions, such as, why an application ran so slowly during a particular time interval, or where an application is spending most of its time. The first step is to establish a baseline level of database resource consumption by understanding the behavior of the average application.
Since Precise for DB2 is now part of the Precise infrastructure, you can drill down to other installed databases like J2EE, Oracle, or Microsoft SQL server. The behavior of the average application represents the average percentage of the total time that all applications spend consuming or waiting for each resource. These aggregate figures provide a broad picture of the operation of your database. They enable you to identify major bottlenecks in your system, for example, CPU and I/O limitations, DB2 database waits due to locks, remote queries, and so on.
Precise for DB2 also enables you to focus on and drill down into individual applications, SQL statements and database objects in your system that are responsible for heavy consumption of system and database resources and to quickly find the definitive root cause of performance degradation.
Integration with other Precise products
Precise for DB2 has been integrated with Insight, Alerts, and Report Manager.
Insight monitors the response times of systems and breaks these times down into the various tiers and components. Insight also provides tier-specific metrics to help understand the activities in each tier. For more information, see the Insight User's Guide or Online Help.
Alerts provides a notification system that will alert you to application response time problems and enables you to launch Precise for DB2 in context to view more detailed data. For more information, see the Alerts User's Guide or Online Help.
The Precise Custom Portal is a lightweight, configurable portal application that provides access to various types of data from different applications. The Precise Custom Portal features a Web-based view. It is highly customizable and extensible, allowing you to build a dashboard for each user or each function within your organization. For example, you can build a dashboard for all the information that a system administrator or director of IT operations would need to constantly follow up on. This component is automatically installed as part of the Precise framework installation. For more information, see the Precise Custom Portal User's Guide.
Report Manager gathers and organizes historical information that enables the IT team to focus on infrastructure hot spots. Report Manager is a reporting tool that resides on a Web server. You can use Report Manager to compare period-to-period performance against a baseline and identify response time problems “at-a-glance,” before they affect your bottom-line. It queries the PMDB and displays the results in reports that are generated on a scheduled basis or on demand. For more information, see the Report Manager User's Guide or Online Help.
The PMDB is the repository of the Precise product suite which stores application performance data for long-term analysis and trending reports. For more information, see the Precise Administration Guide.
Components of Precise for DB2
Precise for DB2 employs a client/server architecture to provide high performance database monitoring and tuning. Precise for DB2 consists of the following components:
- One or more Precise for DB2 Collectors installed on one or more servers in your application
- Precise for DB2 FocalPoint
- Tuner
- Listener
- PMDB
- Collector extension, such as the storage collector or ERP Collector
See the relevant product’s manual.
Precise for DB2 agents
The Precise for DB2 Collectors are installed on servers and communicate using TCP/IP. The Collectors collect and analyze DB2 database performance data, which is then stored in flat historical files and later in the PMDB.
The Collectors monitor and record all DB2 database activity on a 24 x 7 basis. The Collectors work using a sampling technique and thereby consume less resources than a trace agent.
The Collectors collect resource consumption data on the following Precise for DB2 data entities: databases, applications, programs, statements, batches, login names, DB users, and machines.
The following agents are available:
| Agent name | Description |
|---|---|
| Application Performance agent | Precise for DB2 provides a complete view of application performance management by capturing, measuring and correlating performance metrics from all critical system components. |
| SAP™ | If ERP support is installed, the Collector also includes SAP. This agent provides data on the activities and resource consumption of packaged application entities. This data can help you identify ERP users, transactions, reports, and so on, that are heavy users of DB2 resources. The current version only supports SAP. |
| Storage agents | EMC Storage. See “Optional Collector extensions” on page 11. |
Precise Listener and Precise for DB2 FocalPoint
Each Precise product has a FocalPoint that communicates with the listeners installed on the monitored servers. The Precise Listener communicates with other Precise components on the same server. For example, the PMDB FocalPoint requests to load data into the PMDB are sent to the Precise Listener. Then, the Precise Listener executes the PMDB loader to aggregate and prepare performance data to be loaded. In a similar way, InformPoints and Insight agents send requests to the Precise Listener.
PMDB loaders
PMDB Loaders periodically load data—collected by the Collectors and stored in the historical files—into the PMDB. When the PMDB Loaders run, the flat historical files—written after the last time data was loaded into the PMDB—are identified and opened. The summarized data is also loaded into the PMDB. Data on the last incomplete time slice is only loaded into the PMDB when the next scheduled load process runs.
Optional Collector extensions
Optional Collector extensions are available. They provide additional information in certain applications, namely for storage and for Enterprise Resource Planning (ERP). Precise for Storage correlates DB2 database activity with the I/O on the back-end storage. ERP extensions correlate DB2 database activity with front-end ERP users and transactions.
Precise for Storage is available for the following technologies:
- EMC Symmetrix
If storage system support is installed, the Collector provides detailed information regarding the I/O resource consumption across the storage devices. By investigating this information, you can relate I/O activity to specific components of the storage device and pinpoint the cause of the I/O performance degradation.
With Precise for Storage it is possible to browse device-specific mappings and statistics information on EMC Symmetrix arrays.
Support for Veritas File System (VxFS) and Veritas Volume Manager (VxVM) is available if you have installed Precise for Storage and Veritas Mapping Service (VxMS) on Sun Solaris. For more information, see the Precise Installation Guide or contact Customer Support or your SE.
ERP extensions are available for the following technology:
- SAP
If ERP support is installed, the Collector provides detailed information on the activities and resource consumption of "packaged application" entities. It enables you to identify ERP users, transactions, reports, and so on, that are heavy users of DB2 resources and to drill down to the statements executed by them and the objects they accessed. See the Precise Installation Guide or the Precise Administration Guide.
How Precise for DB2 works
Precise for DB2 stores a complete history of your database activity in the PMDB. This provides you with a performance tracking tool that gives you the ability to assess database performance trends and analyze changes in database activities over long periods of time. This data is vital for identifying patterns in resource consumption and predicting future resource consumption in your DB2 database.
The Precise for DB2 Collector runs on your server using a unique, accurate and comprehensive sampling technology that requires low overhead. It collects performance data from an instance and then correlates and loads the data into the PMDB tables. This data is then analyzed and summarized by the FocalPoint. You can later access this data using the Precise for DB2 Web browser interface.
The Precise for DB2 Time Model
An application may wait for many events in DB2. Since the number of events is very large and the meaning of some events is unclear, Precise for DB2 groups various DB2 events into event groups or states and defines the status of sessions currently connected to DB2 by their states. Each DB2 application state is either in DB2 or not in DB2.
In DB2 states
The In DB2 resource consumption graphs give a breakdown of how DB2 is spending its time executing statements and utilities. The wait states with the longest duration indicate the bottlenecks within DB2. Reducing the duration of these wait states improves the response time or alternatively increases the throughput.
The following table lists the existing In DB2 states.
Table 1 In DB2 states
| In DB2 state | Description |
|---|---|
| Using CPU | Time spent by the application using CPU. |
| CPU Wait | Time spent by the application waiting for CPU. |
| Sync I/O Wait | Time spent by the application waiting for a synchronous I/O request to/from the buffer pool. |
| Direct I/O Wait | Time spent by the application waiting for a direct I/O request of LOB or long column to/from the tablespace file bypassing the buffer pool. |
| Prefetch Wait | Time spent by the application waiting for a prefetcher to read into the buffer pool the next group of pages. |
| Lock Wait | Time spent by the application waiting for the release of a lock. |
| I/O Error Wait | Time spent by the application waiting for all active applications using a tablespace to release it in case of I/O error. The application that encounters an I/O error in a tablespace must wait to acquiesce the entire tablespace. |
| Remote Query Wait | Time spent by the application waiting for pending results from a remote federated server. |
| Utility Wait | Time spent by the application while waiting during running of a utility (command) such as: backup, restore, load, unload, restart, acquiesce and create a db. |
Overall Activity Graphs
The Overall Activity resource consumption graphs display information on total time spent in DB2 and a breakdown of activities outside of DB2. Overall Activity can help answer the question: Is the application spending its time in DB2 or elsewhere?"
The following table lists the Overall Activity states.
Table 2 Overall Activity states
| Overall Activity state | Description |
|---|---|
| In DB2 (Summed) | Time spent by the selected item or items in the selected category executing an SQL statement. |
| UOW Waiting | Time spent by the application waiting for a request or input. For example, a Unit Of Work wait takes place when (1) a user starts a Command Line Processor session, connect to the database and then do nothing, (2) a client application executes an SQL statement, displays its result set on a window and waits for next GUI request while still connected, (3) export application writes fetched rows to an output file. |
| Decoupled from DB agent | Time spent by the application when a database agent (process) is not assigned to the connected application. This happens when using connection concentrator (MAX_CONNECTIONS > MAXCAGENTS). It is impossible to tell whether the application is idle or the application is waiting for an available database agent. |
| Connect Pending | Time spent by the application waiting for DB2 to complete connect request. |
| Disconnect Pending | Time spent by the application waiting for DB2 to complete disconnect request. |
| Wait for Remote Request | Time spent by the application waiting for a remote DB2 server to complete SQL execution. |
| Wait for Commit Phase II | Time spent by the application waiting for the coordinator to request DB2 to start commit phase II. This state is also known as an in-doubt transaction. |
What you can do with Precise for DB2
Use Precise for DB2 to detect, diagnose and resolve performance problems in your DB2 database. Precise for DB2 lets you identify real-time performance problems (for example, locking problems) and monitor a complete history of your DB2 database.
You can use Precise for DB2 to:
- See your whole application status at a glance
- Support very large installations – hundreds of databases and partitions
- Resolve performance problems by identifying their root cause and improving statement performance
- Proactively monitor, analyze, and tune Precise for DB2 data partitions
- Analyze and tune locking problems
- Determine whether an I/O problem is caused by a Precise for DB2 application
- Tune a database object rather than a specific statement that accesses the database object
- Perform I/O tuning of storage devices
- Identify the most resource-consuming SQL statement and view a detailed explanation of the execution plan of the SQL statement
- Recommend which index would provide a better execution plan and reduce resource consumption
- Monitor your system's current state as well as previous states
Where to get more information
More information on Precise, its products, technical notes, and so on, can be found in Precise Release Notes for this version.