Filter
Top Products
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.
240 Query for iSeries Use V5R2