IBM SC41-5210-04 manual Query for iSeries performance tuning, Query for iSeries status messages

Query for iSeries performance tuning

A properly tuned system provides much better overall performance than one in which performance tuning has not been used. However, there are many factors to consider when properly tuning the system to meet your needs. See the Work Management topic for details. In addition, the Performance Tools for iSeries book explains how to monitor and understand overall system performance. Use these guides together to help improve overall system performance.

If possible, limit the number of ad hoc queries in order to avoid unnecessary access path builds. Determine which queries are used most often, then create access paths for these queries and save the query definitions. Most users can then operate out of this fixed set of queries and experience much better response time and overall system performance than if everyone runs queries in an ad hoc manner.

Limit access to the Query product on the system to those people who have an understanding of Query performance and how to best use it. This eliminates costly ad hoc queries that can severely affect other users. Have new users read through this guide to introduce them to Query before giving them access to the product.

Consider removing unused data from the files that are actively used and placing it in separate saved files. This significantly reduces the amount of time and resources spent by Query searching through the active files or building access paths over them.

Ensure that all available performance PTFs are applied to the system. This not only includes PTFs applying directly to Query, but also others that may apply to overall system performance as well.

Pay attention to the performance optimization messages that are available in debug mode (use STRDBG before running your query). These messages may help you determine how you can change the query definition so it will run faster.

Query for iSeries migration considerations N to N-1

When query creates a database file that includes a date, time, timestamp, variable-length, or null-capable field, a bit is set that states that this file cannot be used with a release prior to Version 2 Release 1 Modification 1. When query creates a database file that includes a DBCS-graphic field, a bit is set that states that this file cannot be used with a release prior to Version 2 Release 2 Modification 0.

Notes:

1.If you run a query that contains date, time, or timestamp data types on a release prior to Version 2 Release 1 Modification 1, one of the following two things may happen:

vNo records are selected

vResults are in error

2.A query that uses a DBCS-graphic constant will not run on a release prior to Version 2 Release 2 Modification 0.

Query for iSeries status messages

When you run a query interactively, status messages may appear at the bottom of your display to let you know what is happening. For long-running queries, these messages can help determine which stages of the query take the most time to run. Once this is determined, it may be easier to decide which of the previously listed tips and techniques apply. This section lists the status messages that may appear when running a query.

Query running. Building access path for file X in Y.

This message indicates Query has determined an access path is required to run this query, but no existing access path meets the needed values. Query builds an access path and displays this message while the access path is being built. Notice how long this message appears on the

Appendix C. Query for iSeries performance tips and techniques 241