Best practices for tuning DB2 UDB v8.1
Fraser McArthur

Note:  This is an excerpt. The full article can be found on IBM's developerWorks.

Introduction

Performance is a vital key to the success of your on demand applications. When those applications are using IBM® DB2 Universal Database™ as a data store, it's essential that you begin with a fundamental knowledge of how to achieve the best possible performance with DB2 UDB. In this article I'll give in-depth recommendations for tuning a DB2 UDB V8 system.

We'll talk about performance issues from the beginning to the end of the process. You can follow the flow from creating a new database to running with your application. You will see how to use the DB2 auto-configuration utilities to initially configure your database manager and database environment. Then I'll discuss best practices for creating buffer pools, table spaces, tables, and indexes. There are some important configuration parameters you may want to adjust from their initial settings to better support your application, so we'll take a look at those configuration parameters as well.

We'll cover tuning based on monitor output in detail. I'll show you how to use snapshot monitoring to help tune your SQL, buffer pools, and various database manager and database configuration parameters. Then, we'll take a closer look at the SQL your application issues to DB2. We'll cover statement event monitoring, showing you how to capture the SQL your application is issuing. Using Explain, you can generate the access plan that the SQL is taking and look for opportunities to better optimize. We'll examine the Design Advisor, a tool which can recommend new indexes or evaluate existing indexes, based on SQL workloads that you supply to it. Finally, I'll discuss some DB2 SQL options.

In addition, on-going maintenance is very important to maintain optimal performance. So we'll cover the important utilities to help you do this. For those who are using the DB2 ESE Database Partitioning Feature (DPF), I've included a section covering the issues you should be concerned with to keep the database performing well. Sometimes there is an external bottleneck (from DB2) which can prevent you from achieving your performance goals. Common bottlenecks and the utilities you can use to monitor them are listed. Finally, the paper ends with a listing of valuable IBM resources to help you find valuable DB2 information.

Before You Start

Before you begin the performance tuning process, make sure you have applied the latest DB2 fix pack. There are often performance enhancements in fix packs. DB2 FixPak 4 was used as a basis for this article. If you are using a pre-FP4 release, not all options discussed may be available in your environment.

When you are tuning, it is ideal to have a reproducible scenario of database use (that is, the workload your application runs against DB2) that you can use for tailoring your tuning efforts. For example, if the workload has variations of 10% is elapsed time from run to run, it is very difficult to know what effect tuning has really had. Additionally, in cases where the workload varies from run to run, it is hard to measure the changes to the database manager and database configuration parameters.

Always keep track of all changes. This can be useful for the development of tuning scripts or recommendations, as a history for other DBAs, and for backing out of any bad changes.

The "Top 10" Performance Boosters

Here are the top 10 things you can do to get the most performance out of your database. Usually, you will find that about 90% of maximum performance is achieved using about 10% of possible configuration changes. I'll discuss each item in detail in the appropriate section below (identified in parenthesis):

• Ensure that you have enough disks (6-10 per CPU is a good start). Each table space's containers should span all available disks. Some table spaces, such as SYSCATSPACE and those with a small number of tables do not need to be spread across all disks, while those with large user or temporary tables should (Table Spaces).
• Buffer pools should make use of about 75% (OLTP) or 50% (OLAP) of available memory (Buffer Pools).
• Runstats should be performed on all tables, including the system catalog tables (Runstats). Use the Design Advisor to recommend and review indexes for SQL workloads (Design Advisor).
• Use the Configuration Advisor to configure the database manager and database for your application environment (Configuration Advisor).
• Logging should occur on a separate high-speed disk, identified by the NEWLOGPATH database configuration parameter (Experimenting).
• Concurrency can be increased by committing often (SQL statement tuning).
  SORTHEAP should be increased to avoid sort overflows (DBM and DB configuration).
• Table space type should be SMS for the system catalog table space, and temporary table spaces and DMS raw (device) or file for the rest. Run db2empfa to enable multi-page file allocation for the SMS table spaces; this will allow SMS table spaces to grow an extent at a time (instead of a page), which can speed up heavy insert operations and sorts which spill to disk (Table spaces).
• Use parameter markers for repeated statements (SQL statement tuning).