IBM SC41-5210-04 manual Miscellaneous tips and techniques for Query for iSeries

Be careful with using the “NE” comparison between fields from different files on the Specify How to Join Files display. This could result in a large number of records being selected and a large amount of I/O being performed.

Query runs more efficiently when the files are ordered from smallest to largest. In this case, smallest means the file from which the fewest records are selected for the join. Although this can be the file with the least records, in some cases a very large file can be used if only a few records are chosen from that file.

For an option 1 join, Query attempts to order the files from smallest to largest, depending on the number of records selected from each. For an option 2 or 3 join, list the files in this order to achieve more efficient processing.

For an option 2 or 3 join, try to make the files listed first as small as possible by using both join and record selection tests. For example, if the join selection tests is T01.A EQ T02.A AND T02.A GT 100, it would be more efficient to change this to T01.A EQ T02.A AND T01.A GT 100. For an option 1 join, apply as many selection tests as possible to all the files, since you cannot determine which one Query will choose as the primary.

If sort tests must be specified from multiple files for an option 1 join or a secondary file in an option 2 or 3 join, using both record selection and join selection tests becomes important. The smaller the number of records selected, the fewer that have to be copied into the temporary file for the sort, thus saving on processing unit, I/O, and response times.

If you are experiencing severe performance problems when joining large files, either try to avoid this type of operation, or use selection tests to narrow down the number of records being joined.

If you need to join large files and can use record selection tests, run Query against the file or files requiring the record selection and put the output to a database file (option 3 on the Select Output Type and Output Form display). Use this output file to join with the other files. However, this approach may result in using “old” data, since the output to the database file may be an older version by the time the join query using this file is run. Also, since no access paths exist over this output file (unless you build them), Query must build one at run time if it is required.

Miscellaneous tips and techniques for Query for iSeries

This section lists miscellaneous tips and techniques designed to assist you when using Query.

Batch processing for Query for iSeries

Consider submitting queries to batch processing whose results you do not need immediately. For instance, a query that generates printed reports that will not be used until later is a good candidate to submit to batch. This frees your terminal for other tasks instead of waiting for the query to finish running. Also, a properly tuned system is better at balancing system resources (processing unit time, storage, I/O) between jobs if a query that normally uses a lot of resource is submitted to batch rather than run interactively.

The steps to submit a query to batch vary depending on the environment you operate in. In the System/36 environment, press F6 (Put on job queue) to submit a query to batch from the prompt display for QRYRUN. This key is allowed after you specify printer or disk as the output type and press the Enter key. If you are not in the System/36 environment, use the Submit Job (SBMJOB) command to submit a batch job containing a Run Query (RUNQRY) command. From the iSeries system, use the Work with Queries or Exit this Query display to submit queries to batch. See Appendix D, “Preventing users from running Query for iSeries queries interactively” on page 243. For more information on these commands, see the CL Reference information in the iSeries Information Center.

240Query for iSeries Use V5R2