Introduction

Do you need to analyze key performance factors in detail in order to control and tune the performance of DB2 and DB2 applications? Are you expected to proactively diagnose performance and availability problems? Or have you experienced a problem operating your DB2 server, but were not able to determine the cause of the problem using current snapshots, and wished you had historical monitoring data?

IBM DB2 Performance Expert is a tool which can assist you in all these tasks by providing online monitoring, exception processing, and short-term and long-term history monitoring capabilities. Part 1 of this article introduced DB2 PE and illustrated its components. Part 2 took you into the product, demonstrating various practical usage scenarios, such as:

• Determining whether or not an index would improve performance
• Reviewing sort performance
• Checking the need to reorganize tables
• Ensuring that there are sufficient DB2 agents to handle the workload
• Resolving lock conflicts
• Examining frequently used SQL statements from the package cache
• Analyzing buffer pools
• Monitoring system health

Part 3 takes a closer look at the Performance Warehouse (PWH), the component of DB2 PE that saves historical monitoring data over a long period of time (long-term history) and provides tools to analyse the data, including reports, queries, and rules-of-thumb. Analysing the data in the PWH helps you to identify potential performance problems or trends, or to determine the effects of changing a DB2 configuration value. The data stored in the PWH consists of DB2 snapshot data, DB2 event monitor data, and DB2 configuration data.

Back to top

Performance Warehouse

When you're monitoring performance for any system, you should look at the performance data over long periods of time in order to gain a real understanding of system issues. Short-term monitoring is useful for diagnosing an immediate problem (for example a response time degradation), but does not give insight into the long-term effects of system changes or aid in capacity planning for the future. DB2 PE addresses this need by capturing the short-term performance snapshot and configuration data and saving it into a long-term Performance Warehouse. In addition, data captured by the DB2 statement event monitor can be saved into the PWH if needed. While the history data of DB2 PE is only retained for a short time (hours or days), once data is in the PWH, the data is available for reporting and trend analysis over much longer periods.

Here are some of the questions that can be answered by analyzing the data in the PWH:

• What SQL statements are consuming the most CPU, sort, and execution times?
• What are my most frequently executed SQL statements?
• Are my asynchronous read ratio and buffer pool hit ratio always above warning and problem thresholds in peak or normal hours?
• Is a change in the database manager or database configuration the reason for a bottleneck, such as a sort overflow?
• Are my catalog cache and package cache sized properly to handle the workload in peak and normal hours?
• Are the SQL statements running well with the current set of indexes?

Figure 1 introduces the components of the Performance Warehouse. The PWH consists of a set of control and data tables residing in the performance database and is available for each DB2 instance that you monitor with DB2 PE. The data tables contain the actual long-term performance data for the following:

• Buffer pool activity, including table space and table information based on DB2 snapshot data
• Database activity based on DB2 snapshot data
• Database and database manager configuration
• SQL activity based on the DB2 statement event monitor

The control tables are used to save any definitions used to collect, load and analyze the data.

The PWH includes a Workflow Engine consisting of processes that are used to collect and load data and to create reports from the loaded data. You can automate the execution of the processes by scheduling them for times when you wish to collect and receive reports. For example, you might want to get a report each Monday morning about your database activity for the previous week.

In addition, the PWH serves as a data warehouse. Extract, transform, and load (ETL) processes are used to collect and load the data into the PWH tables:

• Extract: Snapshot and configuration data is read from the short-term history tables. SQL activity data is read from files written by the statement event monitor.
• Transform: Snapshot data is aggregated over a period of time. SQL activtiy data is transformed into a relational format.
• Load: Snapshot, configuration, and SQL activty data is inserted into the PWH data tables.

Analysis tools such as reports, predefined and user-defined queries, and rules-of-thumb are available to help you gain insight into the data.

Back to top

Getting data into the Performance Warehouse

There are two ways for performance data to go into the PWH:

• ETL of short-term history snapshot and configuration data
• ETL of SQL activity trace event monitor data

ETL of short-term history snapshot and configuration data

To collect short-term history data, DB2 PE makes periodic snapshots of system activity at an interval you specify, the recording interval. By default, this interval is set to 5 minutes for statistical data such as database, buffer pool, table space, table, or configuration data, but can be adapted to a higher or lower time value. Historical data is stored in the performance database, and the data is used when you display data in history mode on any of the online monitor screens. DB2 PE will keep history data in the history tables for 50 hours by default; after that point the data is deleted automatically. This timeframe can be modified.

At an interval configured by you, the captured short-term history data will be migrated into the PWH tables using an ETL process. During the transformation step, the data is aggregated, which means that short-term history data collected over several intervals is summarized to only one interval in order to reduce the amount of data contained in the PWH. The smallest aggregation interval is 15 minutes.

For example, if you specified that you want to collect short-term history data on statistics counters every 5 minutes and then summarize the history data into 15 minute groups when it is migrated into the PWH, then you will have 3 rows of short-term history data for every one row of PWH data.

PWH aggregation methods

The DB2 snapshot data that is collected consists of the following three types of monitor elements:

• A watermark monitor element indicates the highest (maximum) or lowest (minimum) value an element has reached since monitoring started.
• A gaugemonitor element is an indicator for the current value of an item. Gauge values can go up and down.
• A counter is a representation of information that is cumulative up until the sample is taken. A counter enumerates values that increase, such as the number of deadlocks. Counters are reset when you stop and restart an instance or database.

DB2 PE handles each monitor element type differently when it summarizes them from history into PWH.

• For watermarks, the highest (maximum) or lowest (minimum) value is used.
• For gauge values, average values are calculated because they can increase and decrease depending on the database activity.
• For counter values, delta values are calculated over the aggregation interval.

For example, if you collect short-term history data in 5-minute intervals and have an aggregation in 15-minute intervals, you would see the following:

• For a watermark, the highest or lowest value reached, up to the end of the most recent 15-minute interval.
• For a gauge, the value would be averaged over the seven or eight detail history rows within the 15-minute interval.
• For a counter, the difference in the value between the last and the first history row of the 3 rows.

The aggregation of configuration data (database manager configuration and database configuration data) into the PWH is a bit different from the other elements, because these are not of the types watermark, gauge, or counter. These are informational elements, and they are moved into PWH only if any value has changed from one interval to the next. If your configuration parameters rarely change, you will have only a few rows in the appropriate PWH tables.

PWH data tables containing snapshot and configuration data

After the aggregation, the data is loaded into the following data tables:

• PWH.BUFFERPOOL for buffer pool snapshot data
• PWH.TABLESPACE for table space snapshot data
• PWH.TABLE for table snapshot data
• PWH.DATABASE for database snapshot data
• PWH.DBMCFG for database manager configuration data
• PWH.DBCFG for database configuration data

ETL of SQL activity trace event monitor data

The other type of data stored in the Performance Warehouse is that which is collected by the statement event monitors you can run from within DB2 PE. You can run an SQL Activity Trace from either the Application Summary panel, where you trace one specific application, or as a process in the PWH, where you can trace all applications or some filtered subset of applications for one database. The SQL Activity Trace can be scheduled in advance or run on demand. When you run a SQL Activity Trace, DB2 PE will create a statement event monitor, let it run for the time you indicated on the setup, then stop it and load the event trace data from flat files into the PWH tables. Depending on the type of request, it may also generate a report.

PWH data tables containing SQL activity trace data

The SQL activity trace data is loaded into the following data tables:

• PWH.EVM_HEADER for event monitor, database, and instance information
• PWH.EVM_CONNECTION_HEADER for connection information for each application.
• PWH.EVM_STMT_IDENTIFIER for statement information, such as package, creator or cursor
• PWH.EVM_STMT_TEXTS for statement text data
• PWH.EVM_STMT_SUMMARY for summary information, such as prepares, warnings, errors or executions
• PWH.EVM_STMT_OPERATIONS for statement operations

Back to top

Using Performance Warehouse processes and creating reports

Processes are used to collect report data (CRD), load the data into the PWH tables and create a report. A process consists of up to three steps (CRD, load, report). Not all steps are necessarily performed when you execute a process. For example, if you want to create a Database Activity Report then the data already exists in the PWH tables because it was migrated from the short-term history tables, so only the report step is executed.

Processes are organized in process groups. The existing process group Public contains process templates that you can copy into your own process group, configure, and execute either on demand or on schedule. Figure 2 shows the Performance Warehouse Processes screen. In the tree you see the list of process templates in the Public group and the list of copied processes in the pedemo group. The SQL Activity Summary Report process consists of three steps (CRD, load, report), which can be configured individually.

For the processes that create bufferpool and database activity reports, only the report step is available. To configure the report step, specify the following parameters:

• Database name
• Filter conditions, such as buffer pool name, table space name, table name
• Report interval
• For a DPF environment: partition number or global, which means that the data is aggregated over all partitions.

For the processes that create SQL activity reports, all three steps are available. To configure the steps, specify the following parameters:

• Database name
• Filter conditions, such as application id, application name, or authorization id
• Elapsed time the event monitor should run
• Maximum file size of the event monitor files
• For a DPF environment: partition number(s)

The following sections describe the reports in detail:

• Database activity report
• Buffer pool activity report
• SQL activity report

Database activity report

Database activity reports include the monitor elements that are provided by the DB2 database snapshot API, but they are collected and aggregated over time so you can see the overall, long-term behavior of your system. These types of reports are extremely valuable for trend analysis and for future capacity planning.

Database activity reports can also be useful for profiling a new or changed application in a test environment. For example, you can collect performance data over a series of test cycle runs, keep the data for each run, and compare them later. You can compare the test results with later production results as a way to validate your projections and improve for the future. If you use a strict test process where you can reset an environment back to a "zero state" before each run, the PWH data can be very valuable in proving reproducible results for a test.

The database activity reports are laid out in report blocks, with each section reporting a different grouping of monitor elements. The database activity report has nineteen report blocks:

• Configuration
• Overview
• SQL statistics
• Row statistics
• Sorts
• Hash joins
• Internal DB Statistics / Counts - Logging
• Internal DB Statistics / Counts - Locking
• Internal DB Statistics / Counts - Catalog Cache
• Internal DB Statistics / Counts - Package Cache
• Internal DB Statistics / Counts - Shared Workspace
• Internal DB Statistics / Counts - Private Workspace
• Bufferpools / IO Statistics - Non-bufferred I/O Activity
• Bufferpools / IO Statistics - Data section
• Bufferpools / IO Statistics - Index section
• Bufferpools / IO Statistics - Times
• Bufferpools / IO Statistics - Extended Storage Section
• Bufferpools / IO Statistics - Page Cleaner Section
• Applications

Figure 3 shows a part, the Overview and SQL statisics report blocks, of a sample database activity report.

Buffer pool activity report

The buffer pool reports are similar in layout and format to the database activity reports discussed in the previous section, with each section of information in a report block with aggregated snapshot data for that particular group of elements. The buffer pool report blocks are:

• Buffer pool
• Tables pace
• Table

SQL activity report

SQL activity reports include information about applications running on your database captured by the DB2 statement event monitor. The SQL Activity Trace Report lists the SQL activity in the order it was executed by the application. The SQL Activity Summary Report contains summary information per statement for each application:

• Total number of executions
• Total execution time
• Total sort time
• Number of warnings and errors
• Total number of rows read from table
• Total number of rows changed in table
• Total preparation time
• And so on.

This summary information allows you to determine the heavy hitter SQL statements in your applications.

If your applications execute static SQL statements, then the SQL activity reports show them as well, together with the summary or operational information.

Figure 4 shows a part of a sample SQL activity summary report. The first table lists the summary information for each statement. Using the hyperlinks in the report, you can drill down to the details of each statement, including the statement text and operation information for each execution statement.

Back to top

Using Performance Warehouse queries

Queries are used to analyze various aspects of the data in the PWH. The queries are standard SQL select statements that operate against the PWH tables. You can either use predefined queries or create your own, using standard SQL. DB2 PE provides a column-assist feature in the query builder, so you can pick tables and columns from a list. This is handy if you are not familiar with all the PWH tables, but you do not have to use the column assist. If you are comfortable with SQL, you can just write the query freehand. A really nice feature of the PWH queries is the ability to insert a variable into the query, which you can later fill in at run time.

Like the processes, the queries are organized in query groups. The existing query group Public contains all predefined queries. You can execute them directly from the Public group, but if you want to change a query before executing it, you must copy the query into your own query group, change it, and execute it. Your own queries are defined also in your own query group.

Figure 5 shows the list of predefined queries. The predefined queries are divided into three different groups:

• Problem queries: These queries return only rows that exceed thresholds for important performance counters, for example, rows with buffer pool hit ratio lower than 90%. The predefined queries whose names start with DB2PM.Buffer pool, DB2PM.Database, DB2PM.Table space belong to this group.
• Report queries: These queries return the data contained in the PWH tables under different aspects. You can use these queries to define your own reports. The predefined queries whose names start with DB2PM.Report belong to this group.
• Heavy hitter queries: These queries operate on the PWH tables which have stored the data collected by the DB2 statement event monitor. They return heavy hitter SQL statements, for example, the statement that consumed the most sort time. The predefined queries whose names start with DB2PM.SQL Activity belong to this group.

Figure 6 shows a sample query result for a predefined problem query that was executed. The query returns only the buffer pools that have a lower hit ratio than 90%. In additon, it returns the time intervals when the low hit ratio occurred. You can open the query results in a browser as well, and can save them for later analysis.

The next sections show some queries that might be useful. These are not part of the set of predefined queries. They illustrate the different types of information you can obtain from the PWH tables.

• Show which table spaces use which buffer pools
• Correlate database configuration value with monitor element over time
• Identify trends for log usage
• Compare AVG_APPLS to buffer pool hit ratio

Show which table spaces use which buffer pools

The following query Listing 1 shows which table spaces use which buffer pools. You can get this out of the system catalog of course, but if you extend this query with snapshot data that is also available in these two tables (for example buffer pool hit ratio), then you get a complete picture of the activity in your table spaces and buffer pools. This may lead to decisions such as assigning a table space to a different buffer pool.

SELECT DISTINCT
T1.DB_NAME
, T1.BP_NAME
, T2.TABLESPACE_NAME
FROM PWH.BUFFERPOOL T1
, PWH.TABLESPACE T2
WHERE T1.BUFFERPOOL_ID = T2.TABLESPACE_CUR_POOL_ID
AND T1.DB_NAME = T2.DB_NAME
AND T1.MEMBER_ID = T2.MEMBER_ID
AND T1.INTERVAL_TO = T2.INTERVAL_TO
AND T1.INTERVAL_FROM = T2.INTERVAL_FROM

Correlate DB CFG value with monitor element over time

The following query Listing 2 shows how you can correlate the behavior of particular monitor elements (sort-related elements in this example), with a corresponding database or database manager configuration value, SORTHEAP in this example. This approach is very valuable for evaluating trends and looking for the effects of changing a configuration value. Note that the PWH stores DB and DBM CFG values, but only inserts a row if a value has changed. Each of these tables has the configuration value as a column in the table. The INTERVAL_TO column in the PWH.DBCFG and PWH.DBMCFG tables does not contain the same time as the INTERVAL_TO columns in the other PWH tables. That is why we have the additional predicate in the example below. You should always include this in a query comparing configuration values to statistics values.

SELECT
T1.INTERVAL_FROM AS DB_INTERVAL_FROM
, T1.INTERVAL_TO as DB_INTERVAL_TO
, T2.INTERVAL_TO as DBCFG_INTERVAL_TO
, T1.DB_NAME
, T1.TOTAL_SORT_TIME
, T1.SORT_OVERFLOWS
, T1.ACTIVE_SORTS
, T2.SORTHEAP as DBCFG_SORTHEAP
FROM PWH.DBASE T1
LEFT OUTER JOIN PWH.DBCFG T2
ON
T2.INTERVAL_TO < T1.INTERVAL_TO and T2.INTERVAL_TO >= T1.INTERVAL_FROM
AND T1.DB_NAME = T2.DB_NAME
AND T1.MEMBER_ID = T2.MEMBER_ID
WHERE
T2.DB_NAME = ':db_name'
AND DATE(T1.INTERVAL_TO) BETWEEN ':from_date' AND ':to_date'
ORDER BY T1.DB_NAME

Identify trends for log usage

The following query in Listing 3 reports on some of the log and log space usage monitor elements, together with the number of applications that are currently executing, and allows you to enter a date range. You might be able to track log growth or look for an increase in secondary log allocations, which might indicate some impending space issues in the database. With any query, if you see something that looks unusual, you can always run additional reports or queries to drill down to more information.

SELECT
INTERVAL_TO
, DB_NAME
, TOTAL_LOG_USED
, TOTAL_LOG_AVAILABLE
, TOT_LOG_USED_TOP
, SEC_LOGS_ALLOCATED
, SEC_LOG_USED_TOP
, APPLS_IN_DB2
FROM PWH.DBASE
WHERE DB_NAME = ':db_name'
AND DATE(INTERVAL_TO) BETWEEN ':from_date' AND ':to_date'

Compare AVG_APPLS to buffer pool hit ratio

The following query Listing 4 shows the correlation of the AVG_APPLS configuration parameter, the number of applications that are currently connected to the database, and the buffer pool hit ratio of the database. If you use multiple buffer pools, you might take the buffer pool hit ratio together with the buffer pool name from the PWH.BUFFERPOOL table. The SQL optimizer uses the AVG_APPLS parameter to help estimate how much of the buffer pool might be available at run time for the access plan chosen. Higher values for this parameter can influence the optimizer to choose access plans that are more conservative in buffer pool usage. The APPLS_CUR_CONS value helps you to adjust the AVG_APPLS configuration parameter. The POOL_HIT_RATIO value shows you the effect of changing AVG_APPLS on your buffer pool hit ratio. If your AVG_APPLS parameter is well adjusted, but your buffer pool hit ratio decreases, you might think about tuning your buffer pool, for example by increasing the buffer pool.

SELECT
T1.INTERVAL_FROM AS DB_INTERVAL_FROM
, T1.INTERVAL_TO as DB_INTERVAL_TO
, T2.INTERVAL_TO as DBCFG_INTERVAL_TO
, T1.DB_NAME
, T1.POOL_HIT_RATIO
, T1.APPLS_CUR_CONS 
, T2.AVG_APPLS AS DBCFG_AVG_APPLS
FROM PWH.DBASE T1
LEFT OUTER JOIN PWH.DBCFG T2
ON T2.INTERVAL_TO < T1.INTERVAL_TO and T2.INTERVAL_TO >= T1.INTERVAL_FROM
AND T1.DB_NAME = T2.DB_NAME
AND T1.MEMBER_ID = T2.MEMBER_ID
WHERE T2.DB_NAME = ':DB_NAME'
AND DATE(T1.INTERVAL_TO) BETWEEN ':from_date' AND ':to_date'
ORDER BY T1.DB_NAME

Back to top

Using Performance Warehouse rules-of-thumb analysis

The rules-of-thumb (ROT) function provides a powerful way to analyze long-term data and to present recommendations to the users on how to tune their systems. Once long-term data has been stored into the PWH tables, you can use the predefined ROT or define your own ROT to analyse the data in the PWH.

A single ROT consists of a formula for calculating a ratio or percentage, warning and problem thresholds, and a recommendation for what to do when the warning or problem threshold is breached. ROT functions are organized in clusters. The predefined ROT functions are organized in a buffer pool, database, table space and SQL activity cluster. If you start a ROT analysis, then you can start it either using the whole cluster (which means that all ROT defined in that cluster are used for analyzing the data available in the PWH), or you can start the analysis using only one ROT from a cluster.

The following list shows the predefined ROT functions available in the predefined clusters.

• Buffer pool
  • Hit ratios ( Data, index and both )
  • Asynchronous read ratio/write ratio
• Database
  • Catalog/package cache hit ratio
  • Rows read versus rows selected
  • Sort overflows
  • Files closed
• Table space
  • Asynchronous read ratio/write ratio
• SQL activity
  • Most CPU time
  • Most execution time
  • Most sort time
  • Most frequent statements

Like the processes and queries, the ROT clusters are organized in ROT groups. The existing ROT group Public contains all predefined ROT clusters. You can execute the ROT within a cluster directly from the Public group, but if you want to change a ROT before executing it, you have to copy the ROT into your own ROT group, change it, and execute it. Your own ROTs are also defined in your own ROT group.

Figure 7 shows a sample ROT result matrix that is displayed after you have started ROT analysis on the PWH data. In this example, the buffer pool cluster was used. For each record in the PWH.BUFFERPOOL table, the result matrix indicates whether a warning or problem occurred for the calculated ratios.

From the result matrix you can drill down into row or column details. Figure 8 shows the row details for a selected timestamp. If you select a ROT Name, highlighted in the figure below, then you see in the Rules of thumb details how this value was calculated, its actual value, and the recommendation for what to change in your system.

Back to top

Summary

Performance Warehouse saves historical monitoring data consisting of DB2 snapshot data, DB2 event monitor data, and configuration data over a long period of time (long-term history). It provides functions to analyse the data such as reports, queries and rules-of-thumb. Analyzing the data in the PWH helps you to identify potential performance problems or trends or to determine the effects of changing a DB2 configuration value. In summary, the long-term data in the PWH together with its analysis functions are highly valuable for proactively improving future DB2 UDB system behavior and performance.

Resources

• DB2 Performance Expert for Linux, UNIX and Windows Homepage gives an introduction to DB2 Performance Expert.
  
  
• DB2 Performance Expert for Multiplatforms V2 (IBM Redbook, January 2005) provides guidelines for using the DB2 Performance Expert tool for Multiplatforms.
  
  

About the author

Ute Baumbach is a software developer at the IBM lab in Germany and works as a member of the DB2 Performance Expert development team. In addition, Ute supports customers in setting up DB2 Performance Expert and utilizing it to its best advantage. She is an IBM Certified Database Administrator and a Certified Application Developer for DB2 UDB.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Performance Warehouse
• Getting data into the Performance Warehouse
• Using Performance Warehouse processes and creating reports
• Using Performance Warehouse queries
• Using Performance Warehouse rules-of-thumb analysis
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers