Filter
Top Products
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:
v Any 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.
v Backup 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:
v If 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.
v Query 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.
232 Query for iSeries Use V5R2