Adjust Default Query Optimizer Settings with QAQQINI

by Michael Sansoterra

If you're looking to resolve user-defined function (UDF) timeouts, or want to squeak by with slightly quicker dynamic query performance, look no further than the QAQQINI file. QAQQINI is known as the query options file, because it controls many of the settings the query optimizer uses when executing queries.

Before making adjustments to QAQQINI, you must first create a copy of the default file found in library QSYS. To make the copy, use the Create Duplicate Object (CRTDUPOBJ) command:

/* Create copy to affect all users */
CRTDUPOBJ OBJ(QAQQINI)
          FROMLIB(QSYS)
          OBJTYPE(*FILE)
          TOLIB(QUSRSYS)
          DATA(*YES)

Or the following:

/* Create local copy */
CRTDUPOBJ OBJ(QAQQINI)
          FROMLIB(QSYS)
          OBJTYPE(*FILE)
          TOLIB(MYLIB)
          DATA(*YES)

As shown, QAQQINI can be copied to a user library where its settings will only affect user-specified jobs, or it can be copied to library QUSRSYS, where its settings will affect all users. Further, both copies can be made with the local settings used to override the global settings.

However, in order to change a job to use a copy of QAQQINI in a library other than QUSRSYS, you must first issue the Change Query Attributes (CHGQRYA) command:

CHGQRYA QRYOPTLIB(MYLIB)

Once you have a copy to use, any setting can be adjusted. One setting of interest in this file is called UDF_TIME_OUT. This attribute controls how long the database manager will wait for a user-defined function to process before canceling the statement. The default is 30 seconds. I recently tried to execute SQL statements containing UDFs written in Java on an older machine. The box was so slow that the statement would always timeout the first time, though, because it had to wait so long for the Java Virtual Machine to initialize.

To keep the first statement from failing, I needed to increase the amount of time the database manager would wait for the UDF to complete before terminating the statement. Use the following SQL command to increase the timeout interval:

UPDATE QUSRSYS.QAQQINI         -- Change global copy
   SET QQVAL='180',            -- Wait 3 minutes
       QQTEXT='Allow JVM time to start'
 WHERE QQPARM='UDF_TIME_OUT'   -- UDF Timeout

Here are the columns in the QAQQINI file: QQPARM is the specific setting name; QQVAL holds the new value for the given setting; QQTEXT stores user-defined text, which can be used to describe the nature of the change.

The list of all the QAQQINI settings and their allowable values are available on IBM's Web site.

Here are other useful options:

• MESSAGES_DEBUG. Specifying *YES for this option can be used to send query optimizer messages without starting debug mode. Of course, you wouldn't want to set this as a system-wide option. It's useful for developers who have their own copy of QAQQINI and want to log the optimizer's messages without being in debug mode. It's also useful in debugging a user's job that's already running, as the CHGQRYA command can be used to change the query options file for an active job. (The changes will not affect a running SQL statement.)
  
  
• FORCE_JOIN_ORDER. This option can be used to improve the performance of Query/400 queries and certain SQL statements when you're certain the specified join order of the files is always the best way to get the data. Setting this option to *YES causes the query optimizer to skip the step of attempting to figure out for itself which way is the best to join the tables. This option doesn't hasten the data retrieval, but it does shorten the time users have to wait before data retrieval begins. So it's good for dynamic queries that require a quick interactive response time. Again, this isn't a good candidate for a system-wide setting.

QAQQINI gives you a way to fine-tune the way queries run at the system and job levels. Further, local copies can be used by developers to optimize a query according to the various available settings without affecting queries done by others.

For more information on the setting and their values, check out the following resources:

• "Creating the QAQQINI query options file," in the "DB2 UDB for iSeries Database Performance and Query Optimization V5R2" guide
  
  
• Custom Query Options Builder
  
  
• What is QAQQINI?

Michael Sansoterra is a programmer/analyst for SilverLake Resources, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@silver-lake.com

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL