IBM SC41-5210-04 manual Select sort fields in Query for iSeries

Note: If there is no existing access path matching some of the record selection tests, Query does not build an access path solely for purposes of selection. Query reads each record and selects those that qualify.

If you request a particular query often, consider creating an access path with select/omit tests to match that query. See “Select/omit access paths in Query for iSeries” on page 232 for more information on this subject.

Existing access paths are only used for OR conditions involving the same field specified in the selection tests.

One type of record selection is to use the % symbol with the LIKE operator as a generic search or scan (also known as a wildcard scan). If the generic scan starts in the first position of a field (for example, %ABC), Query cannot use any existing access paths for that portion of the record selection. However, if the generic scan starts after the first position (for example, ABC%), Query can use any qualifying access paths over the field specified in this type of record selection.

Select sort fields in Query for iSeries

Query, in most cases, needs an access path to sequence the selected records when sort fields are specified. If an access path does not exist, Query creates a temporary access path at run time or uses a sort to order the records. A sort routine is used when the optimizer determines that the sort routine provides better performance. If a temporary access path is used, it is deleted after the query has finished running, so each run of the query requires another build of the access path. For this reason, always consider whether you really need sort fields for the query.

Consider creating access paths that match the sort tests for queries that you use often and for queries where the access path build time is excessively long. Query attempts to use an existing access path if all the sort fields from the query match the high order key fields from the access path. This way you can avoid excessive building of access paths for queries with sort tests.

As an example, assume file Z has fields A, B, C, and D. Also assume there are six access paths built over this file that have the following keys specified in this order:

1.Access path #1 has key field A

2.Access path #2 has key fields A and B

3.Access path #3 has key fields A and C

4.Access path #4 has key fields A, B, and C

5.Access path #5 has key fields B, A, and C

6.Access path #6 has key fields A, B, C, and D

Now if you run a query that is defined to sort on key fields A, B, and C, only access paths #4 and #6 are considered by Query during optimization. Access paths #1, #2, and #3 are not used because it is inefficient for Query to read the records again and sort on the additional keys. It is more efficient for Query to build and use an access path containing all the sort and selection tests. Access path #5 is not considered because the sorted keys are not in the correct order.

If a particular query is requested often, consider creating an access path with select/omit tests to match that query. See “Select/omit access paths in Query for iSeries” on page 232 for more information on this subject.

If you have sort tests that you use often, another option (besides creating access paths) is to use Query to sort the records in a database file in the desired order. Query can then be run against this file with no sort tests, if the queries are looking for data sorted as it appears in the file. To perform this function, select the desired sort fields from the file, choose database as the output device (option 3 on the Select Output Type and Output Form display), and specify the output database file name, which must be different from the

236Query for iSeries Use V5R2