Understanding and Using the QAQQINI Query Options File

What is QAQQINI?

Starting with V4R4, all the attributes that can potentially impact the performance of the DB2 UDB for Power i database engine have been centralized in the query options file QAQQINI. This query options file (also known as query performance tune) has the ability to change or override those values dynami- cally. Its central interface and dynamic tuning capability makes it easier to analyze and tune the perfor- mance of database requests.

The query optimizer is responsible for determining the most efficient way to implement a query from any query-based interface: SQL, Open Query File (OPNQRYF), and Query/400. In past releases, the ca- pabilities of the query optimizer were based on the current environment. Attributes were scattered across a wide variety of interfaces: CL commands (e.g., Change Query Attributes (CHGQRYA)), system values (e.g., Query Degree (QQRYDEGREE)), and even data areas (e.g., Query Options (QQQOPTIONS)).

Since these attribute values were spread over many different interfaces, it was a complex process to set all of these different values, and tougher still to determine the value of any one attribute. Additionally, many of these attribute values directly affected implementation decisions made by the query optimizer.

Support of the new query options file QAQQINI provides the ability to dynamically modify or override the environment in which queries are executed through the CHGQRYA command and the QAQQINI file. This query options file can be used to control the performance of a query, or save and apply those configuration settings (attributes) across multiple executions of a query request.

The query options file QAQQINI contains the attributes used by the query optimizer. For each query that is run, the query option values are retrieved from the QAQQINI file in the library specified on the Query Options Library (QRYOPTLIB) parameter of the CHGQRYA CL command. These values are then uti- lized to optimize or implement the query.

Environmental attributes that you can modify through the QAQQINI file include:

• APPLY_REMOTE
• ASYNC_JOB_USAGE
• FORCE_ORDER_JOIN
• MESSAGES_DEBUG
• OPTIMIZE_STATISTIC_LIMITATION
• PARAMETER_MARKER_CONVERSION
• QUERY_TIME_LIMIT
• UDF_TIME_OUT
• PARALLEL_DEGREE

How QAQQINI works

The governor works in conjunction with the query optimizer. When a user requests DB2 UDB for AS/400 to run a query, the following occurs:

1. The query access plan is evaluated by the optimizer.
   As part of the evaluation, the optimizer predicts or estimates the runtime for the query. This helps determine the best way to access and retrieve the data for the query.
2. The estimated runtime is compared against the user-defined query time limit currently in effect for the job or user session.
3. If the predicted runtime for the query is less than or equal to the query time limit, the query governor lets the query run without interruption and no message is sent to the user.
4. If the query time limit is exceeded, inquiry message CPA4259 is sent to the user. The message states that the estimated query processing time of XX seconds exceeds the time limit of YY seconds.You can check the inquiry message CPA4259 for the predicted runtime and for what operations the query will perform. If the query is cancelled, debug messages will be written to the joblog.
   
   Note: A default reply can be established for this message so that the user does not have the option to reply to the message, and the query request is always ended.
    
5. If a default message reply is not used, the user chooses to do one of the following: 

• End the query request before it is actually run. 

• Continue and run the query even though the predicted runtime exceeds the governor time limit.

The query governor can stop the initiation of a query if the query’s estimated or predicted runtime (elapsed execution time) is excessive. The governor acts before a query is run instead of while a query is run. The governor can be used in any interactive or batch job on the AS/400. It can be used with all DB2 UDB for AS/400 query interfaces and is not limited to use with SQL queries.

The ability of the governor to predict and stop queries before they are started is important because:

• Operating a long-running query and abnormally ending the query before obtaining any results wastes system resources.
• Some operations within a query cannot be interrupted by the End Request (ENDRQS) CL command. The creation of a temporary keyed access path or a query using a column function without a GROUP BY clause are two examples of these types of queries. It is important to not start these operations if they will take longer than the user wants to wait.

The time limit is user-defined and can be specified in one of three ways:

• Using the Query Time Limit (QRYTIMLMT) parameter on the Change Query Attributes (CHGQRYA) command.
• Setting the QRYTIMLMT system value and allowing each job to use the value *SYSVAL on the CHGQRYA command.
• Setting the Query Time Limit option in the Query Options File QAQQINI.

Creating the QAQQINI query options file

Each system is shipped with a QAQQINI template file in library QSYS. The QAQQINI file in QSYS is to be used as a template when creating all user-specified QAQQINI files. To create your own QAQQINI file, use the Create Duplicate Object (CRTDUPOBJ) command to make a copy of the QAQQINI file in the library that will be specified on the CHGQRYA QRYOPTLIB parameter. Note that the file name must remain QAQQINI, for example:

CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(MYLIB) DATA(*YES)

Because system-supplied triggers are attached to the QAQQINI file in QSYS, it is imperative that the only means of copying the QAQQINI file is through the CRTDUPOBJ command.

Note: It is recommended that the file QAQQINI, in QSYS, not be modified. This is the original template that should, instead, be duplicated into QUSRSYS or into a user-specified library for use.

Setting values in the QAQQINI file

You can modify different types of attributes of queries that you will execute during a certain job with the CHGQRYA command. The types of attributes that you can modify include:

• Predictive Query Governor
• Query Parallelism
• Asynchronous Job
• Apply CHGQRYA to remote

Where can I find out more about QAQQINI?

• An Online Generator for custom QAQQINI file (query options file), found online in DB2 UDB for AS/400 — Tips and Technical papers, is a Web tool that provides a graphical interface for building an SQL script which can later be executed on your i5 to create a specific QAQQINI file set with the option values that you have selected. This generator is found at IBM’s website.
• For more information regarding QAQQINI, go to DB2 UDB for AS/400 Database Performance and Query Optimization—a section in the DB2 UDB for AS/400 SQL Programmer’s Guide at IBM’s website.