The best solution for optimizing DB2 UDB v8.1 and its database

The following article describes the best practices for optimizing DB2 UDB v8.1 and its databases.®The UDB database and its applications provide the help needed for optimal performance. Contact the author for practice.

This article discusses various stages of development and some skills that can be applied to production systems, including database design and layout, database configuration, SQL statement design, maintenance, monitoring, and other skills.

Introduction

Performance is the key to the success of on-demand applications. When those applications use IBM®DB2 Universal Database™As a data storage, it is important to know from the very beginning the basics about how to achieve the best possible performance on DB2 UDB. In this article, I will give some in-depth suggestions on optimizing the DB2 UDB v8.1 system.

We will talk about the performance issues throughout the process. You can follow the process from creating a new database to running an application. This article shows how to use the DB2 automatic configuration utility to initially configure the Database Manager and database environment. Next, I will discuss best practices for creating buffer pools, tablespaces, tables, and indexes. In addition, you may want to change the initial values of some important configuration parameters to better support applications, so we will also introduce these configuration parameters.

We will discuss the tuning of detailed output based on monitor) to demonstrate how to monitor snapshot monitoring using snapshots) to help tune SQL, buffer pool, various database managers, and database configuration parameters. Next, we will further study the SQL statements that the application sends to DB2. By using Explain, you can generate an access plan for the SQL statement) and find opportunities for further optimization.

We will examine a tool such as Design Advisor that can recommend new indexes or evaluate existing indexes based on the provided SQL load. Finally, we will discuss some DB2 SQL options.

In addition, continuous on-going maintenance is important to maintain optimal performance. So we will discuss some utilities that can help us with continuous maintenance. For those who are using the DB2 ESE Database Partitioning Feature (DPF), I will talk about some of the issues that should be taken into consideration to make the Database run efficiently.

Sometimes there may be some external bottleneck from DB2) that prevents you from achieving performance goals. This article lists some common bottlenecks and utilities used to monitor these bottlenecks. Finally, this article lists some valuable IBM resources to help you discover valuable DB2 information.

This article is intended for those who have intermediate skills in DB2 database management.

Before reading

Before starting the performance tuning process, make sure that you have applied the latest DB2 revision pack ). Modifying a package often improves performance. We will use DB2 FixPak 4 as the basis for this article. If you are using a version earlier than FP4, this environment may not provide all the options discussed here.

During optimization, it is best to have a reproducible scenario about database usage, that is, the workload of the application running on DB2. This scenario can be used to customize the optimization effect. For example, if a workload experiences 10% change in time during different running periods, it is hard to know how the tuning works. In addition, it is difficult to measure the changes in the Database Manager and database configuration parameters if their respective workloads are different during the two operations.

Keep track of all changes. This helps develop optimization scripts or suggestions for reference by other DBAs, and prevents adverse changes.

At the end of most sections, there are links to the relevant sections in DB2 v8 HTML Documentation. You can find online documents at http://publib.boulder.ibm.com/infocenter/db2help/index.jsp.

"Top 10" Performance Enhancement drivers

After doing the following ten things, you can achieve the best performance for the database. Generally, you will find that, with about 10% configuration changes, you can achieve 90% of the best performance. I will detail each of the following items in the appropriate section marked in parentheses:

Make sure that there are enough disks, and each CPU has 6 to 10 disks is a good start ). Containers in each tablespace should span across all available disks. Some tablespaces, such as SYSCATSPACE and those with a small number of tables, do not need to be expanded to all disks, and those with large users or temporary tables should span all disks. Tablespace ).

The buffer pool should occupy approximately 75% of the available memory (OLTP) or 50% OLAP ).

Run runstats on all tables, including the system catalog table Runstats ).

Use Design Advisor to recommend indexes for SQL workloads and check the index Design Advisor ).

Use Configuration Advisor to configure the Database Manager and Database Configuration Advisor for the application environment ).

Logs should be recorded on an independent high-speed drive, which is specified by the NEWLOGPATH Database Configuration Parameter Experimenting ).

Through frequent submission, you can increase the concurrency of SQL statement optimization ).

SORTHEAP should be added to avoid sort overflow DBM and DB configuration ).

For system catalog tablespace and temporary tablespace, The tablespace type should be SMS, and for other tablespaces, The tablespace type should be DMS raw device or file ). Run db2empfa to support space allocation of multi-page files used for SMS tablespace. This will allow the SMS tablespace to grow one partition Extend at a time), instead of one page, which can speed up the large insert operations and the sort operation tablespace that overflows the disk ).

For repeated statements, use parameters to Mark SQL statement optimization ).

Create a database

When creating a database, the System will create three System management Storage systems by default Managed Storage, SMS) tablespace SYSCATSPACE, TEMPSPACE1 and USERSPACE), and a 4 MB buffer pool IBMDEFAULTBP ), the page size of these tablespaces and buffer pools is 4 KB. As recommended below, delete TEMPSPACE1 and USERSPACE and recreate them. This is generally a desirable practice.

In almost all cases, SYSCATSPACE does not need to be further optimized. However, if the container is expanded to several disks, the performance may be slightly improved. Discussed later ).

When creating a database, you can use the automatic configuration option to configure the database according to the environment. This is convenient when an application creates a DB2 UDB v8.1 database programmatically, because these options can be provided from the application to DB2. Another option that must be used to automatically configure a database is the more powerful Configuration Advisor GUI, which can be used to configure databases and instances.

However, to use Configuration Advisor, the database must first exist. We will discuss Configuration Advisor in subsequent sections.

In listing 1, we created a DATABASE in Windows using the automatic configuration option of the create database Command. The DATABASE has a syscatspace that spans two available disks.

List 1. Use the automatic configuration option to create a database

 
 

  
  
Create database prod1 catalog tablespace managed by system using ('C: \ proddb \ cattbs \ 01 ','
  
  
D: \ proddb \ cattbs \ 02 ') extentsize 16 prefetchsize 32 autocon figure using mem_percent 50 workload_type simple num_0000ts 10
  
  
Tpm 20 admin_priority performance num_local_apps 2 num_remote_apps 200 isolation CS bp_resizeable yes apply db and dbm
 
 

Table 1 shows valid input keywords and values for Automatic Configuration:

Table 1. Automatic Configuration Options

Keyword valid value default value description

Percentage of physical storage space allocated to the database by mem_percent 1-100. If the server does not include the operating system) and other applications run on it, set it to a value smaller than 100.

Workload_type simple, mixed, complex mixed simple workloads tend to be I/O-intensive, and most of them are transaction processing OLTP), while complex workloads tend to be CPU-intensive, and most of them query OLAP/DSS)

Num_limit TS 1-1000000 25 number of statements contained in each unit of work

Tpm 1-200000 60 transactions per minute.

Admin_priority performance, recovery, both Optimization for better performance more transactions per minute) or better response time

Num_local_apps 0-5000 number of local applications connected by 0

Num_remote_apps 0-5000 number of remote applications connected

Isolation RR, RS, CS, ur rr the isolation level Repeatable Read, Read Stability, Cursor Stability, and Uncommitted Read of applications connected to the database ).

Bp_resizeable yes, no yes. Can I change the buffer pool size online?

 
 

  
  
Configuration Advisor 
 
 

If you have used automatic configuration when creating a database, this step is not very important. Configuration Advisor is a GUI tool that allows you to automatically configure databases and instances based on your answers to a series of questions. By using this tool, you can often achieve a considerable performance improvement of DB2 UDB v8.1.

This tool can be opened from the Control Center by right-clicking the database and selecting "Configuration Advisor. After answering all the questions, you can generate the results and select the application results. Figure 1 shows the screen snapshot of the result page:

Figure 1. Configuration Advisor Results Screen

For details, visit:

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0404mcarthur/