DB2 database design and highest performance principles

The purpose of this article is to give IBM (R) business partners Some important information about the performance of the DB2 Universal Database (UDB) database in Z/OS (hereinafter referred to as DB2) DB2 (R). This paper attempts to integrate materials from multiple sources and then display them as effectively as possible. This article tries to avoid being too broad in scope and too deep in detail. Next, I'll discuss the factors that most frequently affect the performance of the DB2 database. I am not involved in all possible conditions and all potential considerations here, but only within the scope of expectations. I hope this article will provide a general guide to DB2 customers so that they can obtain the most appropriate performance of the DB2 database in their own environment. This article will start with how to customize the performance of a database in a particular installation.

This article covers the scope of database design performance. And DB2 's performance includes more content than this, in addition to the database design, in fact, there are many other factors. For example, the coding logic of a program, the actual SQL statement used, is divided within the scope of the application design. Factors that affect the performance of DB2 systems include, for example, installation options, buffer pool size, allocation priority for the DB2-related address space, and so on. The focus of this paper is the design of DB2 database. But there are times when the boundaries between these factors that affect performance are somewhat blurred. For example, the size of the buffer pool during installation, and the configuration of the number of buffer pools are generally considered to be system performance factors. However, when assigning a particular tablespace and the indexes of those buffer pools, it is considered to be a database design consideration.

Suppose the reader has some basic knowledge of the DB2 in the z/OS environment. The first few pages of this article discuss some of the basic concepts and principles of performance management for later understanding. My advice is somewhat general in nature, and I always avoid too much entanglement with technical details or grammatical problems. For more information on these issues, you can view IBM's recently published documentation on DB2 installed on the client.

This document focuses on the DB2 in the z/OS V7 environment. Although DB2 has been released and versatile under z/Os V8, it is likely to take several months for most IBM customers to be able to implement DB2 V8 NFM (new feature mode) for their product systems. Here's another question to consider: While every new release of DB2 is fairly well tested in IBM and the customer environment before it is formally generic, customers are still likely to have more confidence based on the previous version of the common recommendations and experiences of DB2. Rather than very much identity has not been released, or is not the general use of a new version (due to the actual process of validating the conclusion of the length and depth of time). I'll mention some new features of DB2 V8 that might affect database design performance.

Disclaimer: The information contained herein is not submitted to any formal IBM test and is provided in accordance with the "as is" principle. The use of this information, or the implementation of any of the technologies mentioned above, is the responsibility of the user and relies on the user's personal ability to assess it and integrate it into the customer's specific operating environment. Each of these will be given an exact value under IBM-specific conditions, and there is no guarantee that the same or similar results will occur elsewhere. When users try to adopt the above technology in their own environment, they need to take risks.

Performance principles and methodologies

Time to consider performance

The time to consider the different performance characteristics of the application and the database is the initial phase of the design of these applications and databases, the beginning of the development process. A reasonable assessment of the resources required by your DB2 application and database will help users make appropriate decisions about design and implementation early in the development process. If your application accesses database performance until later, make the necessary modifications to get enough response time and process your batch window; This will become more difficult and time-consuming.

Focus on

When designing performance, it's wise to focus most of your energy on important DB2 data and programs. Defining an application or transaction is the focus of this part of the work, one or more of the following features are applicable:

They represent the bulk of all business work

They have a critical response time requirement

They include complex logic and/or data access requirements

They access a lot of data

They consume a lot of resources

Instead of interacting directly with customers (via networks, ATM, and so on), applications are mostly geared toward the company's internal

Database design

Database design occurs in the following two phases:

Database Logic Design

Database Physics Design

The logical model of a database is simply a form of normalization of all user data requirements. This model is usually the output of the data modeling phase or the final result. It is rarely implemented in practical sense. Moreover, before considering how to actually organize and store data in a database management system, it is just an idealized view of the data.

When considering the architecture of a particular database object, the logical model is transformed into a physical model. In this design phase, you need to consider some details of the data access requirements and performance factors. In the process of physical design, two key factors are table design and index design. These two issues will be discussed in detail below.

How to DB2 performance management

To ensure that your DB2 application has sufficient performance, taking a positive attitude is more meaningful than a negative response. It is essential to summarize the performance factors in the early stages of DB2 database design. Then in the project, try to identify the metrics that meet your service level agreement (SLA) Performance "Baseline" as early as possible, so you can track performance characteristics and trends as the first demo and application changes. Constantly monitor your DB2 systems and applications to see them before most problems are formed.

Traditionally, many customers began to care about performance issues until the end of an application development project. But such a wait is of no avail. It is more beneficial to think about the performance characteristics of database design as long as it describes the user interface and process logic. For example, in your important DB2 work (see above), one of the basic guidelines for creating the best indexes is to consider the predicates in the SQL statement.

Even if your initial design is an effective design, some of the following modifications are still necessary, such as modifying your application, or the database growing as a volume, or restricting system resources. If an existing application is not fully operational, you will typically want to be able to add more columns to the existing index or add a new index to the table. Whether you change the design of a table, or modify a customer's requirements, or make a table non-standard, it is often not a good choice.