The top 6 tips for a successful DB2 Performance Expert deployment for DB2 for Linux, UNIX, and Windows, Part 1: Modifying settings, setting up alerts, and using System Health data views

Performance monitoring and tuning are critical tasks for a database administrator (DBA). DB2® Performance Expert helps DBAs tackle immediate performance problems, as well as avoid problems in the future. This article is the first of two in a series that describes the top 6 things you can do to get the most out of DB2 Performance Expert.

Share:

Alice Ma, Architect IM Solutions, Warehousing Tools, IBM

Alica MaAlice Ma has over 15 years of experience in the IT industry working with customers and IBM Business Partners to architect solutions, develop, test, and provide advanced support. Currently she works on IBM InfoSphere Warehouse, building solutions specializing in performance and DBA tools at IBM Silicon Valley Lab. Her areas of expertise include problem determination, performance tuning, and recovery for DB2.



Ute Baumbach (bmb@de.ibm.com), Data Studio Software Developer and Customer Advocate, IBM

Author photoUte Baumbach has been a software developer at the IBM lab in Germany for 18 years, where she has worked in various software development projects and roles. Most of her projects were based on DB2. For five years, she has worked as a member of the DB2 Performance Expert development team, now the Data Studio Performance Management development team. Ute is an IBM Certified Database Administrator and a Certified Application Developer for DB2 for Linux, UNIX, and Windows.



30 April 2009

Also available in

Introduction

As a database administrator (DBA), performance monitoring and tuning can be your most challenging business-critical task. You need comprehensive and proactive performance and tuning tools to help you to resolve immediate performance issues as well as to help avoid future performance issues. DB2 Performance Expert helps you optimize your company’s DB2 servers and DB2 applications, whether for data warehousing, for OLTP, or for mixed environments.

Performance Expert helps you quickly isolate current and potential performance problems. Its user interface makes performance and tuning simple so that both novice and expert database administrators can use it. Performance Expert can help you do the following:

  • Optimize buffer pools
  • Tune your database for maximum throughput
  • Improve your database capacity and reliability
  • Obtain a detailed analysis of system performance based on your DB2 for Linux®, UNIX®, and Windows® workload and operating system

You can use operating system counters to help you understand how components outside of the database engine affect your data server.

Performance Expert also offers multiple historical views of performance data. You can see current data, which can help you respond quickly to any emerging conditions, and you can see past performance data, both long-term and short-term. Short-term and long-term history, which is described in greater detail later in the article, can help you troubleshoot problems that occurred in the past, such as over the weekend. You can use the long-term data for trend analysis to help you see patterns of use that you can adjust to prevent problems in the future.

This series describes the top six actions (based on IBM's experiences working with customers) to get the most out of DB2 Performance Expert. This article addresses the first three actions:

Changing the default settings for server history configuration

A powerful feature of DB2 Performance Expert is its ability to provide key performance indicators (KPIs) and configuration data on past, present, and future events to help you understand how your DB2 data server performs today and in the future. To take advantage of Performance Expert’s historical capabilities, it helps to understand a bit about how it works.

Short-term history data

In order to understand your system, take a look at how your system has been running historically. Performance Expert history consists of both short-term and long-term history.

Short-term history provides high-level, detailed statistical counters regarding the DB2 applications, system health, database activity, SQL activity, locking conflicts, Workload Management, operating system (including process, memory, and file system), DB2 configuration parameters, containers, and more. Short-term history helps you troubleshoot problems that occurred during the evening or after end users complain about bad response times. You can also use short-term history data to create performance baselines from a test run and to compare those baselines with other test runs. You can read more about performance baselines later in this article.

Short-term history data is deleted automatically from the performance database after a specified retention time frame. Performance counters of short-term history are automatically aggregated and can be used for long-term trend analysis.

Long-term history data

The long-term history data is not as detailed as the short-term history data. Long-term history data is aggregated from short-term history data, and it resides much longer in the performance database of Performance Expert. Data remains in the performance database forever unless you delete it manually.

Performance Expert can provide a graphical view of your system’s long-term aggregated data, offer trend analysis of the past, and provide future predictions automatically. Reports on the long-term data can help you do the following:

  • Identify long-running SQL by using reports that show which SQL statements are being executed and which can be sorted by differing cost values, including sort time or CPU.
  • Prioritize workloads by storing DB2 Workload Management performance counters and using reports of histogram charts and summary data.
  • Track the number of transactions per day grouped by database name, partition number, and timestamp to help with workload planning.
  • Identify problems with memory, including low buffer pool hit ratios, catalog cache hit ratios, and package cache hit ratios.
  • Identify changes in your dbm and db configuration parameters visually so you don’t need to keep track of those changes.

The reason for changing the default settings

Performance Expert collects performance counters and stores them in a DB2 database, which is called db2pe by default. For each monitored DB2 instance, change the default setting for history collection, because most likely each monitored DB2 instance’s workload is different.

The default setting for short-term history is 50 hours. In many cases, this is not long enough, and you should keep short-term history for 3 to 8 days. The 3-day minimum is to get you through a weekend. One week is recommended. For example, you might have a customer who is experiencing a performance problem on Friday in his development and production environments. On Friday, you focus on fixing his production environment. Then on Monday morning, you work with the DB2 application developers and use Performance Expert’s short-term historical data to pinpoint the performance problem in development. If you don't keep that data over the weekend, that detailed information is not available for analysis.

The drawback of keeping this much data is, of course, disk space, but the benefit of having this data available when needed outweighs the cost in most cases.

Changing the short-term history defaults

To change Performance Expert’s short-term history settings:

  1. Navigate to PE’s System Overview window.
  2. Log into the monitored DB2 instance you want to change.
  3. Right-click the monitored instance, and select Properties from the drop-down box.
  4. Select the History tab, as shown in Figure 1.
Figure 1. Changing the short-term history default settings
Screen cap: Performance Expert Server Properties window with History tab selected and circled
  1. Change the Timeframe value to at least 72 hours (for 3 days).
  2. After you make changes, click OK, and your changes are dynamically changed on the Performance Expert server for this monitored instance. You do not need to restart the Performance Expert server.

Here are more recommendations for short-term history settings:

  • If you are monitoring a warehousing environment, you want Performance Expert to gather performance counters every 5 to 10 minutes. A data warehouse is often associated with an On-line Analytical Processing (OLAP) system that is designed for optimum read capability, which is why you can gather performance counters at longer intervals than you might for an OLTP system.
  • You should also consider changing the Performance Expert recording interval to more than 60 seconds. The value you put in the recording interval is a multiplier for any component history setting (which you can find below the recording interval on the History properties page). For example, if your recording interval is 60, and you specify 5 as the System multiplier, you get 60 x 5, which is 300 seconds (5 min).
  • You should also enable statistics for locking conflicts if locking problems might happen on your monitored system that you want to further investigate. By default, locking counters are not gathered. This interval value should be smaller than the LOCKTIMEOUT database configuration parameter.

Setting up alerts

When it comes to performance and tuning, you don’t have the time to constantly look for potential performance problems, so you need alerts to help you identify performance problems before they become severe. Performance Expert can send out both warning and error alerts using email or text message. Typically, you then go to the dashboard (System Overview) and drill through the highlighted items to find the source of the problem.

How alerts are displayed

When a warning level alert occurs, Performance Expert highlights it in yellow. Red is used to highlight those alerts that have gone beyond warnings to actual problems, as shown in Figure 2.

Figure 2. Alerts are displayed in red in the System Overview screen
Screen cap: FRUITS is highlighed in red shading, indicating that buffer pool is throwing a warning level alert

You can double-click any of the key performance indicators from the Performance Expert System Overview window or dashboard to drill down into more key performance indicators. In Figure 2, the Buffer Hit Ratio for the FRUITS Buffer Pool is extremely low. By double clicking it, you go to more details, as shown in Figure 3. Figure 3 shows the hit ratio highlighted in red, which indicates a performance problem.

Figure 3. Key performance indicators that generated an alert
Screen cap: FRUITS statistics show Hit ratio, Data Hit ratio, and Async Write Percentage circled with red Xs

Click here for a larger view of Figure 3.

Types of alerts

Performance Expert provides two types of alerts:

  • Deadlock event exception, which is used to help investigate when deadlocks have occurred
  • Periodic exception processing alerts (threshold alerts), which are based on operating system and DB2 performance counters

It is easy to set up alerts by setting filters and qualifications on a data counter. For example, you can define a threshold alert to be triggered if your Buffer Pool Hit ratio drops to below 60% for a warning level or 55% for a problem level. If a warning is triggered, Performance Expert sends you an email and highlights the item on the System Overview in yellow. Then when the problem level is triggered, you receive another email, and the item is highlighted in red on the System Overview. You can filter on buffer pool name, partition number, and other properties.

The thresholds are defined in a threshold set, which is a list of Periodic Exceptions performance counters. You can find a list of these threshold sets in the Performance Expert Exception Processing wizard. You can use one of Performance Expert’s pre-defined threshold set templates for different workloads, such as OLTP, Content Manager, and Business Intelligence/Warehousing. You can modify and save these pre-defined threshold set templates, or you can create your own template from scratch.

Recommendations for defining thresholds

If you are new to DB2 or you are not sure what to define as warning and problem levels for counters or even what counters you should be looking at, use one of the predefined threshold templates. Then when an alert is triggered, you can examine what was triggered and why. You might need to modify the template based on your system resources.

Using filters (also called qualifiers) on thresholds is an excellent idea. You can put a filter on a particular threshold counter. For example, if you are monitoring a Content Manager system, you can define an alert to be triggered when the ICMFL32 TableSpace Disk Usage percentage is greater than 90% full for a warning level and 95% full for a problem level. Some DBAs prefer to keep their threshold sets generic or to limit the qualifiers they place on the counters because they use the same threshold set for multiple DB2 instances. You cannot assign two different threshold sets to run against the same DB2 instance.

If you’re running a mixed environment, then combine the OLTP and BI/Warehousing threshold sets, then edit or remove the additional counters. Follow these steps to combine the thresholds sets:

  1. From the PE System Overview window, click the Exception Processing icon the first icon in the row of icons at the top left side of the screen (the first icon in the row of icons at the top left side of the screen).
  2. Right-click Threshold sets > New Threshold Set.
  3. Enter Mixed Env for the name of your threshold set, enter your name as author, and click the Predefined radio button.
  4. Click Statistics BI for Threshold for BI/Warehousing, and click OK.
  5. When the threshold opens up, select Threshold set > Add, and select to add the OLTP threshold set, as shown in Figure 4.
Figure 4. Predefined threshold templates
Screen cap: New Threshold Set screen with Predefined Templates selected and with the Statistics BI template selected

Using your System Health data views

Performance Expert provides you graphical data views called System Health Data Views. These give you an overall understanding of the health of your system for performance and tuning. You know that old saying: "A picture is worth a thousand words." Performance Expert has system health data views for your DB2 and Operating System Performance Counters. You can use these system health data views to diagnose and tune performance problems.

Performance Expert provides 40 predefined data views, including:

  • Average number of locks per application
  • Average number of rows read per selected row
  • Catalog and package cache hit ratio (%)
  • Compare application hit ratio of buffer pools
  • Compare application hit ratio of databases

You can also create customized data views. Performance Expert also includes Data View groups that ship with Performance Expert Client for the Warehousing environment, for the Business Intelligence environment, or for the Content Manager environment.

Note: You can look at both current and historical data in a data view, even one you just created.

Defining your system health charts using predefined data views

First, some terminology. A data view is a system health chart used to focus on a performance problem or issue. An example of a data view is a chart to display the SQL statement distribution. SQL statement distribution shows the distribution of dynamic and static SQL statements for a database.

Performance Expert enables you to create a group of different data views into data groups. Most DBAs create data groups around a specific performance counter. For example, you might create a group for sorts and include data views related to sorting. Some key data groups include:

  • SQL
  • Sorts
  • Page I/O
  • Storage
  • System statistics, including CPU, memory, and disk space

Recommendations for using a predefined data view

Here’s how to configure a data group called SQL that uses two predefined data views to see SQL statement throughput and to sort overflows.

  1. From the System Overview window, click the DB2 instance, and click the System Health button on the toolbar at the top of the window, as shown in Figure 5.
Figure 5. Performance Expert System Overview window icons
Screen cap: Performance Expert System Overview window icons showing the System Health icon (6th from the left) highlighted
  1. From the toolbar, click Selected > New, as shown in Figure 6.
Figure 6. Create a System Health data group
Screen cap: DB2 - System Health window with New under the Selected menu highlighted

Click here for a larger view of Figure 6.

  1. For the Data Group name, enter SQL.
  2. After you create the new SQL data groups, navigate to the new group from the tree of the System Health window.
  3. Right-click that group (SQL) and select Open Predefined Data View, as shown in Figure 7.
Figure 7. Create a System Health predefined data view
Screen cap: Shows Open Predefined Data View menu selection highlighted

Here’s how to create a data view for Sorts, which is in the example SQLdata group.

  1. From the right side of the System Health window, right-click SQL, and select Rows read vs Selected.
  2. Select Sort overflows [%], and click OK.
  3. Select Sort overflows [%] Data View, then click OK, as shown in Figure 8.
Figure 8. Select from a predefined System Health data view
Screen cap: Open Predefined Data View shows Sort Overflows [%] highlighted
  1. Repeat steps 1 and 2, but select SQL stmt throughput, as shown in Figure 9.
Figure 9. Select the SQL statement throughput predefined data view
Screen cap: Open Predefined Data View shows SQL stmt throughput highlighted

Notice that the Predefined Data View window includes a brief description to help novice DB2 users understand how this will help them.

Create a customized data view

If you don’t want to use one of the predefined data views, you can create your own. Here is how to create a customized System Health data view that compares the number of rows read to the number of rows selected. This customized data view is added to the SQL Data Group you created in the previous section.

  1. Navigate to the SQL Data Group you created, and click New, as shown in Figure 10.
Figure 10. Create a customized System Health data view
Screen cap: Shows New menu selection highlighted
  1. On the Category tab, enter Rows Read vs Rows Selected, and click Statistic Details, Databases, as shown in Figure 11.
Figure 11. Customize your System Health data view
Screen cap: Shows New Data View windows with Rows Read vs Rows Selected in Data View Name field and Statistic Details, Databases highlighted in Data View Category field
  1. Click Next to go to the Counters tab.
  2. Select the database in which you want to compare counters.
  3. Scroll down in the Select counters section to select Rows read and Rows selected, as shown in Figure 12.
Figure 12. Select the counters
Screen cap: Shows New Data View windows with checkmarks beside Rows Read and Rows Selected
  1. Click Next to continue to the Thresholds tab.
  2. Select the Dynamic scale radio button, as shown in Figure 13. In this window, you can define a threshold for this chart. This threshold definition draws a line across the chart to highlight that a threshold has been reached. Some people confuse the threshold displayed on this chart with the thresholds defined in Exception Processing. The threshold on this chart does not trigger an alert. Only the counters defined in the Exception Process can trigger an alert.
Figure 13. Customize your System Health data view by defining a threshold
Screen cap: Shows New Data View windows with Dynamic Scale radio button selected
  1. Select the Graphics tab.
  2. Select the bar chart radio button, and choose the colors and patterns you want to use, as shown in Figure 14.
Figure 14. Customize your System Health data view by creating a bar chart
Screen cap: Shows New Data View windows with Rows Read and Rows Selected showing an aqua color and a solid, filled-in pattern
  1. Click Finish. You’ll see in the next section what the final result looks like.

See Resources for more information about creating customized data views.

Using the DB2 Performance Expert history function to go back in time

  1. From the System Health screen, click History.
  2. Click the right arrow key to step back through past events. Even though you just created this chart by following the steps in this article, you can still use the Performance Expert history to go back in time to examine data for Rows Read vs Rows Selected.
  3. Position your mouse pointer over the Data View chart, and hold down your left mouse button to display the actual values, as shown in Figure 15.
Figure 15. System Health data view customized chart
Screen cap: DB2 - System Health showing a bar chart on which flyover values for Rows Read and Rows Selected are displayed

Click here for a larger view of Figure 15.

Creating data view reports in HTML format

DBAs wonder whether it’s possible to generate reports of the Performance Expert System health. The answer is “Yes, it’s possible to generate reports.” To generate the report, right-click the chart, and select Print from the pop-up window, as shown in Figure 16.

Figure 16. Create a report
Screen cap: Rows Read vs Rows Selected window with righ-clicked menu showing Print highlighted

A browser opens displaying the chart legend, the report, and the graph in an HTML format.

Importing System Health data groups for a warehousing environment

To import predefined data groups, follow these steps:

  1. Right-click Data Groups from the System Health window, and select Import, as shown in Figure 17.
Figure 17. Importing System Health data group
Screen cap: Data Groups right-clicked showing Import highlighted
  1. Notice the variety of System Health data groups that have been created specifically for warehousing and business intelligence, as shown in Figure 18.
Figure 18. Select one of many System Health predefined data groups and show charts
Screen cap: Open window shows list of .xml files defining data groups
  1. Select BI_DataViews_Workload.xml from the window, and click Open.

The predefined charts open, as shown in Figure 19.

Figure 19. Predefined warehousing/BI charts
Screen cap: DB2 - System Health window shows four BI - Workload charts

Conclusion

This first part of the series introduced you to DB2 Performance Expert for Linux, UNIX, and Windows. It explained how to get the most out of Performance Expert by changing your default settings for server history configuration, setting up alerts, and using your System Health data views. Stay tuned for Part 2, where you’ll learn to take performance baselines, to take advantage of DB2 Workload Management, and to configure monitoring in large data partitioning environments.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=386434
ArticleTitle=The top 6 tips for a successful DB2 Performance Expert deployment for DB2 for Linux, UNIX, and Windows, Part 1: Modifying settings, setting up alerts, and using System Health data views
publish-date=04302009