DB2 best practices for basic design, performance, and manageability

Over the past few years, many books have been produced to discuss best practices in DB2 design and implementation, including a large number of books and articles. These books delve into how to design DB2 and integrate it into an application. To provide some basic guidance for customers and business partners to understand, this article compiles a series of best practices to consider when using ibm®db2® technology to design and implement applications. The goal of this article is to achieve a more efficient and efficient DB2 implementation. This article does not provide detailed performance best practices for existing DB2 customers and independent software developers (ISVs).

Brief introduction

DB2 for Linux, UNIX, and Windows provides a database platform to build robust, on-demand business applications on this platform. DB2 provides optimal management of relational data, and also provides optimal management of XML data in DB2 9.

This article assumes that you understand the fundamentals of relational database Systems (RDBMS) and implement applications with DB2 or other RDBMS. This article also provides some common best practices to help you base your performance design and create an RDBMS environment that is easy to operate and maintain. At the same time, this article does not consider all the areas of database design, such as security, backup/recovery, and failover. This article also lists some common techniques in other areas, such as performance, but does not discuss database analysis and design issues in these subject areas in detail. This article also provides additional resources to provide you with a broader range of information on all DB2 subject areas. The common best practices listed in this article can simplify the deployment, management, and performance improvement of DB2-related applications to help people who are responsible for application deployment.

General recommendations

A large number of DB2 technical materials are available to help people who are responsible for implementing applications with DB2 for Linux, UNIX, and Windows. Classifying information from a variety of sources is a tricky business for both novice and experienced practitioners. It is recommended to start looking for DB2 technical materials available on the Web from the following sites.

The information Management DeveloperWorks zone contains this article, as well as a number of other DB2 articles, a number of technical materials, demo code, and a downloadable trial version of the product. The information Management DeveloperWorks technical Library contains a wide range of content, including technical articles, product documentation, and tutorials.

A great deal of DB2-related writings can be obtained. One of the best sources of DB2-related publications is IBM press. Specifically include the following books:

"Understand db2:learning visually with examples". The second edition of the book, written for DB2 V9.5, will be available by the end of 2007.

"DB2 V8 Handbook"

"IBM DB2 Version 9 New Features"

In addition, you can check out IBM redbooks, which contains technical tips for DB2 and related information Management products.

For business partners, you can obtain useful information about IBM products, including DB2, from IBM Virtual Innovation Center (VIC). You need to register to be a PartnerWorld member to access VIC. To access DB2 Course Material, after registering, select all courses by product under shortcuts at the bottom right of the VIC home page. You will then jump to a Web page titled "Product Selections". Follow the prompts to access the course materials related to the product.

Design and implementation Skills

Using DB2 migration Tool Kit

When migrating a database from an RDBMS to DB2, you can use DB2 migration Tool Kit (MTK) for the supported source RDBMS, which includes SQL Server, Oracle, Sybase, and MySQL. Tables, views, indexes, RI constraints, user-defined functions (UDF), and some procedures can be migrated through this tool. The quality of the UDF and process migration depends on the complexity of the source code. An assessment is also required to determine whether to optimize (refine) code based on the migrated UDF and process, or to encode from the beginning. Some important data types need to be considered when using MTK:

Always match the DB2 column data type with the application variable data type or the JDBC set method. This can improve data access efficiency and potentially eliminate some SQL errors.

Use the smallest data type that meets the requirements of your application. For example, if appropriate, use SMALLINT (2 bytes) instead of INTEGER (4 bytes). In some cases, MTK migrates a source DBMS data type to DECIMAL (x,0). In order to improve processing efficiency and save space as much as possible, you should consider using INTEGER or BIGINT instead of DECIMAL (x,0) data types. However, when this decision is made, the data type of the result of the operation must be taken into account.