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:
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:
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:
The time limit is user-defined and can be specified in one of three ways:
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.
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:
Still have questions? We can help. Submit a case to Technical Support.