Filter
Top Products
Each time you run a saved query, Query validates the access plan by checking that the files and access
paths named in the plan still exist.
If it is valid, Query uses that plan to access the data. This can result in a significant performance gain,
when compared with running queries without stored access plans, because Query does not have to
validate the access path when running a stored query. However, the difference may not be significant for
some queries if this validation is only a small part of the processing time.
If the access plan is not valid, Query tries to find the best access plan to use in accessing the data, and
performance may be affected.
The saved access plan is not used if you:
v Override the output form when date, time, or timestamp data is included
v Override the output type from display to printer or database file or vice versa
v Use a query from a prior release
v Override an input file (OVRDBF command)
v Override a file selection using the run query (RUNQRY) command
v Run a query with language sequence collating on a system with a different national language. This
applies if Use Collating Sequence for all Character Comparisons processing option is set to NO.
v Run a query with a change in weighting values or CCSID of the collating sequence selected at run time.
This applies if Use Collating Sequence for all Character Comparisons processing option is set to NO.
v OS/400 forced a rebuild due to system program changes (PTFs).
Note: In some cases, when you press ENTER (to save a query), it may take longer than expected to
save the query because the system is defining an access plan for the query. However, once the
access plan is defined for the query, the performance advantage can be significant for both the
individual query response time and the system in general, especially if the query is run often.
Updating access plans in Query for iSeries
To update an access plan for a saved query, enter the change option for that query and save it again. (You
do not need to make any changes.) This allows Query to update the access plan to reflect any changes. If
you have a large number of saved queries, it is useful to understand which queries are affected by which
access path changes. Then you do not have to change and save all the queries when a change is made
to one or more access paths.
Access plans for stored queries are not updated to reflect access paths that were deleted or created since
the last time the query was saved. Query notes these kinds of changes when it validates the access plan
and reoptimizes. Although the reoptimization may find a better method of accessing the data, this better
method is not automatically updated in the access plan. This means that the next time the query is run,
reoptimization occurs again because the access plan still reflects the original method chosen at the time
the query was saved.
File definitions and data in Query for iSeries
This section lists considerations for defining files and the actual data in the files.
File definitions in Query for iSeries
Note whether numeric field definitions within a database file on the iSeries system are in the zoned or
packed decimal format. The iSeries system performs arithmetic operations using the packed decimal
format.
In the packed decimal format, two digits are stored in each byte, except the low-order byte. The low-order
four digits of the low-order byte contain the sign of the number. For example, the binary representation of
+123 in the packed decimal format is 0001 0010 0011 1111. In the zoned decimal format, the digits are
Appendix C. Query for iSeries performance tips and techniques 233