- Optimizing SQL Performance for AS400 (iSeries)

Optimizing SQL Performance for AS400 (iSeries)

There are a variety of methods or tools to determine how the performance of an SQL statement can

be improved on an AS/400. Find a reference(s) to SQL statement optimization techniques for AS400.

Compare SQL Performance Against Non-WOW Methods

Try taking the SQL statement from WOW and use one of these other methods to compare the results:

• AS400's STRSQL

...or...

• Put the SQL into iSeries Navigator (Run SQL Scripts)

If the SQL statement performs poorly by methods other than WOW, most likely the problem lies with the

SQL statement or the file itself.

Using STRDBG

You can use debug messages for performance hints. On the AS/400:

• • Turn debug on for your session è STRDBG *ALL.

  • Run the SQL statement (to be run in the operation) using STRSQL.

  • After successfully running the statement, look at the job log for debug messages from the query

    • optimizer.

Using iSeries Navigator (STRDBMON)

The iSeries Navigator version of the STDBMON is called a detailed SQL performance monitor. You can

start this monitor by right-clicking SQL Performance Monitors under the database portion of the iSeries

Navigator tree and selecting New-> Detailed. You can monitor a single query or all queries. Once the

monitor is started, it appears in the SQL performance monitors list.

For more details on iSeries optimization, see the “Optimizing Query Performance Using Query Optimization

Tools” section in the "Performance Optimization" Reference.

Controlling How the Data is Accessed

On the iSeries, how the data from a file is accessed is determined by the query optimizer. If an index exists,

the index is used. Otherwise, that decision is left to the query optimizer, which may not be the most

efficient means. For more information on indexes (iSeries), see the "Performance Optimization" Reference.