IBM SC41-5210-04 manual File definitions and data in Query for iSeries

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:

vOverride the output form when date, time, or timestamp data is included

vOverride the output type from display to printer or database file or vice versa

vUse a query from a prior release

vOverride an input file (OVRDBF command)

vOverride a file selection using the run query (RUNQRY) command

vRun 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.

vRun 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.

vOS/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