IBM SC41-5210-04 manual Performance tips for join operations in Query for iSeries

Option 2—Matched records with primary file

A record from the primary file is selected regardless of whether there is a match with any of the secondary files. The selection of primary records is dependent on the select/omit criteria specified on the Select Records display. Only primary records that meet those criteria are selected. Select/omit criteria specified against secondary files may cause a record from the primary file not to be selected.

Option 3—Unmatched records with primary file

A record from the primary file is selected only if there are no-matches with all of the secondary files.

For option 2 and option 3 joins, which file is listed as the primary and which as the secondary is important to the end result produced by the query, since ordering these files differently can produce different results. However, the order is not important for option 1 joins since the same result occurs regardless of the order in which files are placed.

This difference is important when considering how Query performs a join. Since the order of the files in an option 1 join is not important, running this type of query may result in Query choosing a different ordering of the files to gain better performance at run time. For example, if a query defines file A as the primary and file B as the secondary, at run time Query may actually decide to use file B as the primary if it provides better overall performance for the query. In options 2 and 3, however, Query cannot rearrange the order of the files since this can produce different results, so the primary and secondary files always remain as listed in the query definition.

Note: Although Query may choose to rearrange the order of the files at run time for an option 1 join, Query never alters the actual query definition.

Performance tips for join operations in Query for iSeries

For all join operations, Query requires the use of an access path over each of the secondary files in the join. If no usable access paths exist, Query builds them as needed. For this reason, if a particular join query is run often or if several join queries use the same sort or join selection tests, consider building access paths that match these values so Query does not have to build them each time you run the queries.

Note: Query does not require an access path on the primary file unless there are sort fields selected from this file.

It is important to build access paths to match join selection tests you use often. The access path or paths should match the fields selected from the secondary files. For example, if the join selection test is T01.A EQ T02.A, an access path is required over T02.A. For an option 1 join, Query may decide to switch the order of the files and also internally switch the order of the join selection to match the new order. In this case, the previously created access path may not be used. For an option 2 or 3 join, however, Query does not switch the order of the files, so existing access paths that match the join selection tests on the secondary file should be usable for the join.

Use as many record selection and join selection tests as possible on all files to be joined to narrow down the number of records that will result from the join operation. This significantly reduces the amount of I/O required to run the query.

If possible, limit using *ALL on the Specify How to Join Files display. If *ALL is used, the number of joined records produced could be large. For example, if you use *ALL to join a file containing 2000 records with a file of 3000 records, the end result would be 6000000 joined records. A large amount of I/O would be required, resulting in a long response time and some degradation in overall system performance.

Appendix C. Query for iSeries performance tips and techniques 239