IBM SC41-5210-04 Select/omit access paths in Query for iSeries, Access plans in Query for iSeries

Note: Having existing access paths is important because a temporary access path created by Query is not saved. It must be created each time that particular query is run.

Select/omit access paths in Query for iSeries

Use the CRTLF command to create access paths with select/omit tests specified in the DDS.

Specify the select/omit tests in such a way that they match part or all of the selection tests from one or more queries. Specifying an existing access path should improve performance because Query then does not have to find or build a usable access path.

Using a select/omit access path also can save time when defining a query because the selection and sort tests specified in the access path need not be repeated in the query definition.

Note:

Query may use a select/omit access path even if it is not specifically named in the Specify file selections portion of the query itself. However, if the select/omit access path is created with the Dynamic Selection (DYNSLT) keyword in the DDS, there is no performance gain over nonselect/omit access paths.

The select/omit access path can be used if it is a superset of the selection criteria. For example, if the selection criteria specifies an action path 'X GT 45' and a select/omit access path (logical file) exists with a selection of 'X GT 40', then the existing logical file may be chosen by the optimizer.

Considerations for creating access paths in Query for iSeries

Not all access paths can be used by all queries, so create access paths that you use often, either by one query that is run a great deal or by several queries that can all share the same access path. To determine which access paths that Query can use and other general tips on how to define your Queries to improve performance, see “Defining queries for Query for iSeries” on page 234.

Creating a minimum number of access paths is important for these reasons:

vAny change to a field in a database results in updating all access paths keyed on that field as well. This can be expensive in terms of performance for a large number of access paths.

vBackup and restore time may increase considerably if a large number of access paths are saved along with the files.

In addition to the tips provided in this section, there are two other general guidelines that may help you determine whether an access path can be used for a particular file:

vIf the query selects over 20% of the total number of records in the file, it generally does not use an access path for that file. Instead, it accesses the records sequentially. However, if the query contains sort tests, an existing access path may be used or a temporary access path may be created even if the 20% guideline is true. The optimizer usually chooses to implement the sort using sequentially read records instead of an access path.

vQuery does not usually create and use access paths for small files. Although “small” in this case is defined as files with approximately 1000 records, this is not a rule, just a general guideline. However, if sort tests exist in the query, an existing access path may be used, or a temporary access path may be created for the file. The optimizer usually chooses to implement the sort using sequentially read records instead of an access path.

Access plans in Query for iSeries

When you save a query definition (whether it is for a new query or a revised query definition), an access plan reflecting the best method for accessing the data is saved along with it.

232Query for iSeries Use V5R2