IBM SC41-5210-04 Define result fields in Query for iSeries, Select records in Query for iSeries

retrieve the text for each field. Also, showing the text for each field results in fewer fields being shown on each display so you have to page through more displays to retrieve the fields you need to view. This is also true for query, file, member, and format lists.

Define result fields in Query for iSeries

Labeled durations are added or subtracted in left to right order. This could make a difference in your results. For example, adding 1 MONTH + 1 DAY could give a completely different result than adding 1 DAY + 1 MONTH Jan 28 + 1 DAY + 1 MONTH gives -> Jan 29 then Feb 28. Jan 28 + 1 MONTH + 1 DAY gives-> Feb 28 then Mar 1.

Define numeric result fields with odd lengths instead of even lengths to reduce system processing unit time when using these fields. See “File definitions and data in Query for iSeries” on page 233 for details.

Avoid defining a result field using division by zero. Although the system processes the query with this present, each divide by zero operation causes error handling by the system that is expensive in terms of processing unit and overall response time.

Avoid defining a result field that causes an overflow condition. Overflow occurs when a field is larger than its specified length. When overflow occurs, Query shows these result fields with the ‘+’ character on the report.

Avoid defining variable-length character fields. Use numeric constants for the offset and length of a SUBSTR function.

Select and sequence fields in Query for iSeries

This option allows you to control which fields appear in a report and where they appear within a report record. To prevent unnecessary disk I/O by Query, select only the fields you need. Also, additional unneeded fields make a report less readable. If you want to use most of the fields, use F21 (Select All) to show all fields, then delete the sequence numbers from the fields you do not need.

Note: If you select no fields, Query for iSeries (as a default) picks up to the first 500 fields in the file. Avoid this type of operation because it causes unnecessary disk I/O.

Avoid using variable-length fields and null-capable fields. Both of these attributes require extra processing.

Select records in Query for iSeries

Specify record selection tests using fields that match key fields of existing access paths or to create access paths that match often-used record selection tests. Query attempts to use an existing access path if at least some of the record selection tests match the first key field of that access path.

For example, assume there is a file X with fields A, B, C, and D. An access path exists over this file using the key fields A, B, and D, in that order. For any query with record values using field A, Query considers using this access path. However, if the record selection does not involve field A, the access path is not used. For instance, if the record test is A EQ 3, the access path can be used. If the query contains record selection tests involving only fields B or D (for example, B EQ 5 or D EQ 8), this access path cannot be used.

Performance improves if more of the selection tests match more key fields in the same access path. This allows the access path to reduce the number of records selected. As an example (using file X again), if the record tests are A EQ 3 AND B EQ 5 AND D GT 8, the access path can be used to find records matching all three of these values.

Appendix C. Query for iSeries performance tips and techniques 235