IBM SC41-5210-04 manual Introduction to Query for iSeries query processing

Appendix C. Query for iSeries performance tips and techniques

This appendix provides guidelines for improving the performance of the Query for iSeries product. These guidelines help you better understand how Query works and which key items to keep in mind for performance when designing or changing a query.

This appendix does not discuss all variations of queries, but instead provides tips and techniques that help with the majority of queries running on the iSeries system. You need to determine which tips and techniques apply to your own particular queries.

The information in this appendix is divided into the following sections:

vIntroduction to Query Processing

vFile Definitions and Data

vDefining Queries

vUsing Join Operations

vMiscellaneous Tips and Techniques

vQuery Status Messages

Introduction to Query for iSeries query processing

Query processing involves the following stages:

vValidating the query and evaluating the best method for retrieving the requested data

vPerforming the input/output (I/O) for this data

vPresenting the data in the requested format

Query often overlaps these stages to provide the best possible response time.

In the first stage of running a query, called optimization, Query determines the fastest way to process a query. An access plan results and is used to perform the actual I/O for the query.

Optimization includes factors such as file size, selection tests, and sort tests. However, the main performance element for both optimization and I/O is the use of keyed sequence access paths for the files selected by the query.

Keyed sequence access paths in Query for iSeries

A keyed sequence access path describes the order in which records in a database file are read. Use the Create Logical File (CRTLF) command to create access paths with keys specified in the data description specifications (DDS).

During optimization, Query uses existing keyed sequence access paths to obtain an approximation of the number of records the query will return. This information is needed in the optimization itself. Also, Query uses existing access paths, if possible, to do the required I/O. Depending on the circumstances, Query may choose to build a temporary access path to complete the query request.

Without existing access paths, Query either must read every record in each file to determine if it meets the values in the query or build a temporary access path if Query requires one. These options can be expensive in terms of processing unit, I/O, and storage requirements and can result in longer response times.

© Copyright IBM Corp. 2000, 2002 231