Brqx Official Site - Documentation Base

Performance Analysis and Tuning for DB2 UDB

Tim Foxon
Metron Technology Limited
Tim.Foxon@metron.co.uk

This paper is based on the Author's experiences in performance analysis and tuning for DB2 UDB on distributed platforms.  The paper is intended as an introduction to DB2 performance, particularly for those who already have a grasp of relational database concepts through products like SQL Server or Oracle.  The paper compares and contrasts DB2 and Oracle, describes the primary sources of DB2 performance and resource consumption data, and covers memory management, physical data storage, indexing & sorting, and locking.  The paper concludes with a summary of some new features expected in DB2.

 
1.   Introduction

The IBM engineers who were working on System R in the early Seventies would no doubt have been astonished had they been told that they were about to give birth to a huge world-wide industry worth many billons of dollars.  Several database management systems have been developed, based on that original work by IBM.  In what has become a bitterly competitive industry, the consensus of opinion is that only three major offerings will survive: SQL Server from Microsoft, Oracle from Oracle Corporation and DB2 UDB from IBM.

Microsoft's SQL Server is only available for Windows.  Versions of Oracle are now available for the vast majority of platforms that are in use in the industry today.  IBM take a middle course, with versions of DB2 available for their proprietary z-Series mainframes, Windows, and the major UNIX implementations including Linux, IBM's own AIX, HP-UX from Hewlett Packard, and Solaris from SUN Microsystems.

This paper concentrates on DB2 UDB for distributed systems and is intended as an introduction to DB2 performance concepts for those with some experience of other DBMSs.  It is by no means an exhaustive or complete picture, but highlights the topics that I have found to be most useful, and have the greatest potential impact on performance.

Section 2 compares and contrasts DB2 UDB with Oracle.  No attempt is made to assess their relative merits.  The choice of DBMS must, of course, be driven by the application and end-user requirements in each case.

Section 3 outlines the primary sources of DB2 performance and resource consumption data.  The following four chapters form the bulk of the material covering memory management, physical data storage, indexing & sorting, and locking.  The paper concludes with some important new features that will be provided in the upcoming new release of DB2.