Business Intelligence

by
Michael W. Cain
iSeries Teraplex Integration Center

IBM eServer Solutions

December 2003, Version 3.0

Download this white paper ( 480 KB).

© Copyright IBM Corporation, 2003. All Rights Reserved.
All trademarks or registered trademarks mentioned herein are the property of their respective holders.

Introduction

On any platform, good database performance depends on good design. And good design includes a solid understanding of indexes and column statistics: how many to build, their structure and complexity, and their maintenance requirements.

This is especially true for DB2 UDB for iSeries, which provides a robust set of choices for indexing and allows indexes to play a key role in several aspects of query optimization. On the IBM eServer iSeries platform, the use of indexes is a powerful tool, but also requires some knowledge on their application.

This paper starts with basic information about indexes in DB2 UDB for iSeries, the data structures underlying them, and how the server uses them. In the second part of the paper, index strategies are presented. Part three discusses additional indexing considerations related to maintenance, tools, and methods. Parts four and five cover column statistics and statistics collection strategies. And finally, the appendices provide examples and references.

This paper provides an initial look at indexing and statistics strategies and their effects on query performance. It is strongly recommended that database administrators, analysts, and developers who are new to the iSeries server or SQL, attend the "DB2 UDB for iSeries SQL and Query Performance Monitoring and Tuning" workshop. This course will teach the developer the proper way to architect and implement a high-performing DB2 UDB for iSeries solution. More information about this workshop can be found at: ibm.com/eserver/iseries/service/igs/db2performance.html

Because the AS/400® system (the predecessor to iSeries servers) was designed before SQL was widely used, a proprietary language and set of APIs were made available for relational database creation and data access — Data Definition Specification (DDS) and OS/400® file commands. Also known as the native database interface, DDS and OS/400 file commands can still be used for creating DB2 objects on iSeries servers.

Because of this native, non-SQL interface, some iSeries developers and consultants will use terminology not familiar to those coming from a pure SQL background. Here is a mapping of that terminology:

Due to the integrated nature of the iSeries database, both the native and SQL interfaces are almost completely interchangeable. Objects created with DDS can be accessed with SQL statements; and objects created with SQL can be accessed with the native record level access APIs. The DB2 UDB SQL interface is compliant with the SQL-92 entry level standard and has implemented over 90% of the updated standard, SQL-99.

[Next]