IBM @server iSeries, User Manual

Page: 249 / 294

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:
v
Any 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.
v
If 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