IBM SC41-5210-04 manual Select collating sequence in Query for iSeries

input or original file name. This produces a new database file containing the sorted records. The time required to produce the sorted records may vary, depending on whether Query has to build an access path to do the sort.

Consider these items before performing this type of operation:

vAny changes to the original file are not automatically reflected in the sorted file unless you provide for this with some type of additional maintenance. For this reason, you may want to limit the use of this option to those files that are infrequently changed.

vIf an access path is built over an unsorted field in the sorted file, use of this access path by Query results in records being returned in an unsorted order. Limit building access paths over a sorted file unless the access paths are built over sorted fields in that file.

If possible, avoid sorting on defined result fields. This can be an expensive operation in terms of performance, since Query must build an access path to satisfy this type of request.

Select collating sequence in Query for iSeries

Use this option to specify an alternative collating sequence. For example, you may want to change a query so that all lowercase letters sort before uppercase letters. If you specify an alternative collating sequence and the query contains character sort keys, Query cannot use existing access paths and must build an access path to do the sort.

Note: Using an alternative collating sequence does not affect numeric, DBCS-only, DBCS-graphic, date, time, or timestamp sort fields.

Using job-run collating sequence choices can cause a query to take longer to run.

Do not use a collating sequence if it is not needed. If your query involves only numeric fields, change a defaulted collating sequence other than hexadecimal to hexadecimal.

A unique-weight sort sequence table might require less processing than a shared-weight table because it can be ignored for comparisons that do not involve evaluating relative order.

Specify report summary functions in Query for iSeries

Access paths do not help performance for summary functions, so you do not need to consider creating access paths solely for this type of function. However, if the query has selection or sort tests specified along with summary functions, an access path matching these values may help improve the overall performance of the query.

If you are familiar with the use of the DB2 UDB for iSeries program on the iSeries system, consider the use of DB2 UDB for iSeries views for Query summary functions. The DB2 UDB for iSeries program allows you to create views based on summary functions against the fields in a file. For example, you can build an DB2 UDB for iSeries view to contain the sum and average for a field in a file. The performance advantage of an DB2 UDB for iSeries view can be significant for Query summary functions, especially in terms of reduced response times. To use a view, specify the name of the view as the file to be selected.

Select output type and output form in Query for iSeries

If you only need to view the summary records, specify this on the Select Output Type and Output Form display. This eliminates the unnecessary time it takes to page through the detail records.

If you are only interested in viewing the first display or two of results from a query, you may choose to view results to a display instead of a printer or database file. Query provides the first display of data as quickly as possible, so viewing one display from the work station is generally much quicker than waiting for the query to generate all results to a printer or a database file.

Appendix C. Query for iSeries performance tips and techniques 237