Benefits

The enterprise-scale performance management tools in DB2® Performance Solution Pack can provide measurable savings to your business, including reduced capital expenditures, consistent service level attainment, increased productivity, and customer satisfaction.

Savings

Reduced capital expenditures

DB2 Performance Solution Pack accommodates growth by using existing resources more effectively. The lack of an effective performance management solution often causes organizations to invest capital that could be put to better use elsewhere. They are either forced to purchase more server processors, memory, and disk storage than they really need, or they are forced to devote costly staff time to research and resolve performance problems.

Service level attainment

High-performing databases and applications enable your business to meet its service level agreements (SLAs). As custom and packaged applications make increasing demands on databases, poor system performance can result in costly penalties in the form of unfilled orders, reductions of service, and revenue loss.

Increased productivity

Good performance management increases employee productivity. When information management systems are unavailable, business users abandon the slow system and change tasks, adding unproductive churn. On an enterprise scale, the tiniest percentage of increased productivity can add up to millions of dollars in savings each year.

Customer satisfaction

To maintain customer satisfaction and loyalty in today's business climate, information systems must be available and responsive. A customer who waits for minutes instead of seconds for an ATM machine to display an account balance is a frustrated customer. Unresponsive customer-facing applications can lead to dissatisfaction and lost revenue.

Capabilities

DB2 Performance Solution Pack provides the end-to-end features that your enterprise requires to move from a reactive model of performance management to a proactive one. It delivers a comprehensive approach to performance management that is built upon the following aspects of problem resolution and prevention:

  1. Identify
  2. Diagnose
  3. Solve
  4. Prevent

Identify

The first step in resolving a performance problem is to identify the source of the problem. You also have to determine whether the event is an isolated incident or an ongoing problem. To identify performance issues quickly, performance management strategies must include 24x7 monitoring, proactive notifications, and automated alerts.

Tivoli® OMEGAMON® XE for DB2 Performance Expert identifies performance problems across the entire DB2 and z/OS® environment, and DB2 Query Monitor identifies poorly performing SQL.

Use Tivoli OMEGAMON XE for DB2 Performance Expert to monitor the performance of all system components

This powerful tool offers the following features to help you identify performance issues:

  • A full range of system and application monitors for viewing performance data
  • An integrated real-time monitor with zoom capabilities that can access any level of detail that your business requires
  • Extended insight into the performance of distributed applications that access data on DB2 for z/OS

To effectively identify performance issues, an end-to-end solution requires both transaction and SQL monitoring. You can monitor your DB2 environment continuously or on a periodic basis. You can flag exceptions and issue proactive notifications when performance falls below user-defined thresholds.

Tivoli OMEGAMON XE for DB2 Performance Expert provides 24x7 monitoring of the entire DB2 and z/OS environment, and can display the following views in real time:

  • Performance data
  • z/OS system statistics
  • Currently running applications and concurrency problems
  • DB2 system configuration parameters
  • The contents of the dynamic and static SQL statement cache

Although less-than-optimum SQL represents 80 - 90 percent of performance problems, Tivoli OMEGAMON XE for DB2 Performance Expert provides comprehensive information about the performance of the entire environment. The following examples describe potential bottlenecks that Tivoli OMEGAMON XE for DB2 Performance Expert can help you identify:

  • Activities and statistics in the logical partition (LPAR) on z/OS, such as enclave information from the workload manager (WLM)
  • SQL statements
  • Transaction processing activities in the DB2 subsystem, such as volume allocation, units of work, writer threads, statistics, and the dynamic statement cache

    Multiple units of work can operate in a single thread. Tivoli OMEGAMON XE for DB2 Performance Expert monitors thread pools, deadlocks, and memory pools.

  • DB2 system configuration parameters
  • Locking conflicts
  • Application activity
  • Activities in the network layer, including TCP/IP performance
  • Activities in DB2 gateways that use DB2 Connect™
  • Web server activity
  • Buffer pools
  • Utilities
Use DB2 Query Monitor to monitor the performance of queries and the applications that use them

DB2 Query Monitor provides 24x7 monitoring of SQL activities across all participating DB2 subsystems in a data sharing environment.

This full-featured tool makes it easy to perform the following essential monitoring functions:

  • Identify problematic queries in the browser interface of the Consolidation and Analysis Engine (CAE)

    DB2 Query Monitor helps you to identify the applications that use a particular SQL statement.

  • Drill down for details about query performance
  • Send the SQL text of the most expensive queries to DB2 Query Workload Tuner or DB2 SQL Performance Analyzer for more analysis and tuning

DB2 Query Monitor identifies queries that put excessive demands on DB2 and affect the performance of other running applications. You can produce alerts when queries cross thresholds that you define, such as processor or getpage thresholds. DB2 Query Monitor shows you information about the SQL to help you identify the source of the offending SQL.

Use DB2 Query Monitor to automate responses to performance issues

You can configure monitoring profiles in DB2 Query Monitor to define actions that are based on user-defined performance thresholds. DB2 Query Monitor can take corrective actions automatically, including the following types of actions:

  • Sending proactive email notifications and alerts
  • Running command scripts
  • Issuing write-to-operator (WTO) messages
  • Making configuration changes
  • Canceling threads
  • Initiating SNMPv1 and SNMPv2 traps

Diagnose

After you identify the problem, the next step is to diagnose it. DB2 Performance Solution Pack simplifies the collection of diagnostic information for technical staff of any level of experience. It evaluates complex application infrastructures and their multiple technology layers to find the links between applications and the SQL that runs in the database. You can determine the precise location where a transaction is spending too much time, and you can analyze the root causes of poor performance, such as resource shortages or exceptional conditions.

The Extended Insight features in Tivoli OMEGAMON XE for DB2 Performance Expert measure elapsed time for every step in a transaction, including key indicators for distributed applications. You can evaluate workload response time per user, and review how much time a transaction spends in each of the following areas of the application:

  • DB2
  • The application program in the application server
  • The network

DB2 Query Monitor provides rich diagnostic capabilities that determine root causes of performance issues that are related to SQL and application processing. DB2 SQL Performance Analyzer optimizes the performance of SQL statements without having to run the SQL in production. DB2 Query Workload Tuner optimizes SQL performance in an IBM® Eclipse interface.

Collect key performance indicators (KPIs)

You can configure the Extended Insight features of Tivoli OMEGAMON XE for DB2 Performance Expert to collect all relevant KPIs and make them available from any location. With 24x7 access to these KPIs, you no longer have to rely on manual processes to collect diagnostic information.

Perform trend analysis

If monitoring operations flag a transaction that is taking many times longer than expected, you can perform trend analysis to determine which system components are affecting transaction response times.

Tivoli OMEGAMON XE for DB2 Performance Expert has a Performance Database and Performance Warehouse that store detailed historical information about performance. You can collect information from many different sources, including System Management Facilities (SMF), the generalized trace facility (GTF), and the OMEGAMON Collector. Rules of thumb (ROT) analysis enables you to create formulas against columns in the performance warehouse for analysis. For example, you can find all packages that consumed more than .5 seconds of processor time.

In addition to identifying top performance offenders, DB2 Query Monitor can also analyze historical data to flag applications or statements that show the greatest incremental increases in run time. Automated notifications inform you about any sudden spikes that are inconsistent with previous trends.

Collect real-time snapshot data

Tivoli OMEGAMON XE for DB2 Performance Expert can start synchronous traces that collect detailed performance data. You can collect almost any relevant information, including concurrent running threads, and generate real-time alerts.

Collect event trace data

Tivoli OMEGAMON XE for DB2 Performance Expert can also start asynchronous traces to gather more details about the following types of events:

  • Start and end of paired events, such as SQL, SCAN, I/O, and lock waits
  • End of thread events
  • Timeouts and deadlocks
  • Statistics intervals
Make insightful comparisons

Features available in DB2 Query Workload Tuner make it easy for anyone to review the following types of comparisons, regardless of skill level:

  • Statistical results of different access plans in diagram format
  • Available actions
  • Differences between table joins
  • Differences between table access and operations
  • Differences in PLAN_TABLE records
Indexes

Some performance problems call for adding one or more indexes. However, unused or obsolete indexes also impede performance. With the virtual drop capabilities that are built into DB2 Query Workload Tuner, you can easily remove unnecessary indexes from the database.

Solve

A performance challenge usually has many possible solutions, and it is not always obvious which one is best. One SQL statement or user identifier in isolation cannot provide a complete picture. If you apply an improvement strategy that worked for one SQL statement to a different type of statement, it might not produce the same results.

Expert advice that is provided by Tivoli OMEGAMON XE for DB2 Performance Expert helps you to resolve problems quickly, regardless of experience. DB2 Query Monitor monitors running queries, query workloads, and DB2 objects, and then sends data to other components for analysis and tuning. DB2 SQL Performance Analyzer generates expert advice in an ISPF interface to optimize the performance of SQL statements and applications. Expert advice features in DB2 Query Workload Tuner also optimize queries and query workloads.

Generate expert advice automatically

DB2 Performance Solution Pack delivers the information that you need to optimize performance quickly.

The following examples demonstrate some of the potential performance bottlenecks for which you can retrieve expert advice automatically:

  • I/O access

    Bottlenecks at the I/O level indicate problems with indexing or access methods.

  • Processor consumption
  • Network throughput

You can use the advisors and comparison capabilities in DB2 Query Workload Tuner to optimize query design, statistics quality, and index value.

The following examples demonstrate how expert advisors can improve performance:

  • Satisfy queries without reading the data rows at all.
  • Improve index value by creating and consolidating indexes to reduce scans and avoid sorts.
  • Improve query design by recommending an additional predicate that selects a different index.
  • Improve the quality of your statistics by identifying the ones that are missing or out of date.
  • Generate a statistics profile based on an analysis of your query workload and then insert the profile in the DB2 Statistics Profile table for the use of DB2 auto stats.
Analyze tradeoffs at the workload level

Unlike other performance management tools, DB2 Performance Solution Pack can analyze entire workloads. A workload might contain thousands of SQL statements, and you cannot get a complete picture by analyzing each statement in isolation. DB2 Performance Solution Pack identifies an optimal strategy by balancing competing concerns across the entire application.

The following examples demonstrate some of the tradeoffs that DB2 Performance Solution Pack evaluates to optimize a workload:

  • When you collect more statistics, you increase processor cost.
  • You can increase speed by adding indexes to a query, but doing so introduces delays in transaction processing.
  • A performance improvement strategy for one SQL statement in an application can deprecate performance for other statements.

Prevent

If you can tune queries and workloads proactively, you can address performance problems before they occur. However, a proactive approach often requires senior DBAs to devote large amounts of their time, which is a luxury that many organizations cannot afford. Without access to costly professional skills, you cannot conduct adequate reviews of SQL performance and the physical design of the database before deployment. Senior DBAs are typically busy with more urgent priorities than query optimization or preventive measures, which leaves them little time to address the large volume of SQL in a workload.

An effective prevention strategy goes beyond addressing performance problems that you are already aware of. You must also be able to analyze and understand trends that might not be obvious. Performance databases in DB2 Performance Solution Pack provide a historical view that gives you the following advantages:

  • A deeper understanding of baseline performance levels that you can use to measure current performance
  • The ability to analyze historical performance information during heavy workload periods
  • The ability to evaluate whether a proposed performance enhancement has unintended consequences or it yields a true net benefit
  • Perspective on areas that consistently present problems over time
Store performance data for analysis and reporting

Tivoli OMEGAMON XE for DB2 Performance Expert reduces total cost of ownership by supporting background processing with zIIP technology to minimize the cost of collecting performance statistics and other historical data. You can store trace data in a performance database to help you analyze performance issues over time, and deploy a Performance Warehouse to automate processing and reporting.

Generate meaningful reports

By using DB2 Performance Solution Pack, you can produce reports to any level of detail that your organization requires, from raw data to summaries per time interval. The information that reports provide helps you to make strategic decisions about costly processor consumption. Batch reports that are based on trace-level information identify performance issues wherever they occur, and in many cases, before they occur. Reports help you to analyze, simulate, and optimize buffer pool activity, which further reduces total cost of ownership. All of this data is critical input to defining an effective prevention strategy.

DB2 Performance Solution Pack supports capacity planning by providing a full range of formats for analyzing performance data. The following examples show some of the report formats that you can use to present performance-related information:

  • Charts
  • Pivot tables
  • Statistical summaries
  • Accounting reports that describe TOP issues

    For example, you can report the top 50 packages that use the most processing cycles, or the top 50 packages that take the longest to run.

  • Detailed lock and latch reports that help you to resolve locking issues
  • Exception reporting
  • Reports that help you to review potentially problematic queries before promoting your application to a production system
Parent topic: IBM DB2 Performance Solution Pack for z/OS overview