DB2 Database design: Guidelines for achieving optimal performance

Many of the design decisions made early in the development process have a huge impact on the performance of DB2 applications and databases. This article provides some general guidelines and recommendations for better performance in the z/OS environment.

First, Introduction

The purpose of this article is to provide IBM business partners with information about DB2 Universal Database? (UDB) for z/OS (hereafter referred to as DB2) an important information about DB2 database performance in the environment. This article attempts to collect materials from multiple places and articulate them as effectively as possible. This article is not intended to contain a comprehensive range, nor does it contain deep details.

I had intended to discuss some of the most influential factors in the performance of the DB2 database. But not all possible scenarios can be predicted, and not all potential considerations are taken into account, let alone describe them in the desired context. I hope this article can provide a common guide for DB2 users in different environments to help them achieve the best DB2 database performance. The purpose of this article is to be a good starting point for dealing with database performance issues in any given installation environment.

The scope of this article is database design performance. DB2 performance is much more than this, and it must be influenced by a number of factors outside the database design. For example, the coding logic of a program and the actual SQL statements used therein can be classified as application design. DB2 system performance can include factors such as installation options, buffer pool size settings, scheduling priorities for DB2-related address spaces, and so on.

The focus of this paper is the design of DB2 database. However, sometimes the line between these performance factor categories may be somewhat blurred. For example, when configured in an installation environment, the size of the buffer pool and the number of choices are often considered a system performance factor. However, if you assign a particular table space and index to those buffer pools, then these factors can be considered as a kind of database design factor.

Here, I assume that the reader has a basic understanding of the DB2 in the z/OS environment. The first few pages of this article discuss some of the basic concepts and guidelines for performance management for "level setting." My advice is a bit of a synthetic nature, and therefore does not always give technical descriptions and syntax in detail. Readers who want more detailed information about the content should read about the most recent IBM documentation for the DB2 version that the user has installed locally.

The common design point for this article is DB2 for z/Os V7. Although the DB2 for z/Os V8 has been announced and is widely available (generally available, GA), most IBM customers may take months to implement DB2 V8 NFM for their production systems (New Function Mode). And there is another factor to consider here. Although each new version of DB2 has been tested extensively in IBM and its customer environments before it becomes generally available, it is not a widely used version of the Customers tend to be more confident about general advice and tips based on earlier versions of DB2 (a long cumulative experience validates this conclusion). I'll mention some of the new features of DB2 V8, which, from a performance standpoint, may affect database design.

Disclaimer: The information contained herein is published in the form of as is without any formal IBM testing. The use of this information and the implementation of any of these technologies are the responsibility of the user, and will depend on the user's ability to evaluate them and integrate them into the customer-specific operating environment. Although IBM has reviewed each item for specific cases, there is no guarantee that the same or similar results can be obtained in other situations. Users who try to apply these technologies to their own environment must take risks themselves.