|Maximize SQL Query Engine (SQE) Usage of Your DB2 Web Query Reports|
|Database - DB2|
|Written by Gene Cobb|
|Tuesday, 08 January 2008 19:00|
Ensure top-notch DB2 Web Query reports by using QAQQINI, exit points, database monitors, and Visual Explain.
In July of 2007, IBM announced DB2 Web Query for System i, a product positioned as a query and reporting tool for the System i and the strategic enhancement to the Query/400 tool. DB2 Web Query has many advantages over Query/400. Most notably, it provides modernized development and end-user interfaces via a Web browser as well as modern report output formats such as HTML to the browser, Excel spreadsheets, and PDF. For more information on DB2 Web Query, download the IBM Redbook titled Getting Started with DB2 Web Query for System i.
In this article, I explain how DB2 Web Query accesses data from the DB2 for i5/OS database and how you can use various tools available on the System i to influence certain optimization behaviors. The ultimate objective is to ensure that DB2 Web Query reports are using the latest IBM technologies and obtaining optimal data access performance.
When accessing data from DB2 for i5/OS, DB2 Web Query uses one of three data adapters:
· DB2 CLI—Generates an SQL statement and submits a Call Level Interface (CLI) API request to the database engine.
· DB Heritage File—Generates an OPNQRYF CL command to submit to the database engine. Used to access multi-member files and multi-member logical files.
· Query/400—Uses the RUNQRY CL command to run an existing Query/400 query (*QRYDFN object).
Of the three adapters, only DB2 CLI can leverage the database's new SQL Query Engine (SQE). SQE is a totally redesigned query engine and was first introduced with V5R2 of i5/OS. It employs efficient, advanced techniques when optimizing queries, creating access plans, and accessing data. Prior to SQE, all query requests were processed by the Classic Query Engine (CQE).
DB2 for i5/OS actually uses both engines to handle all query requests. As of V5R4, the most common items and environments that prevent SQE from executing an SQL statement have been these:
· Non-SQL interfaces (Query/400, OPNQRYF, QQQQry API)
· Upper, Lower, or Translate function
· National language sort sequence
· User-defined table function (UDTF)
· DDS LOGICAL file references on FROM clause
· Select-Omit or derived logical files (non-standard indexes) defined on underlying table
This means that even though DB2 Web Query generates and submits an SQL statement to the database, there may still be cases in which the request is processed by CQE. For most of the restrictions listed above, the only way to obtain SQE processing is to completely remove the offending condition. However, if you are one of many shops that uses Select-Omit logical files in your applications, read on, as there is a method to allow peaceful Select-Omit logical file and SQE coexistence.
Avoiding CQEMany legacy System i applications rely heavily on the use of Select-Omit logical files; therefore, the underlying database tables, when referenced by an SQL statement, are ineligible for SQE processing. It is important to clarify that the mere existence of a Select-Omit logical over a table will cause the query to go down CQE, even when it's the table, not the logical file itself, that is referenced in the statement. Consequently, when any SQL interface (including DB2 Web Query via the DB2 CLI adapter) references such a table, the query will be optimized and processed by CQE. In most cases, this is not the desired behavior.
In a DB2 Web Query environment, you can influence the optimizer to ignore the fact that these Select-Omit logical files exist, thereby clearing the path for SQE processing. While it is true that a possible remedy would be to remove those Select-Omit logical files, it is a solution fraught with risk because existing applications likely rely on the business logic contained in these files. It is better to craft a solution that will not impact the behavior and environment of other applications.
To minimize disruption while accomplishing the stated objective, two System i technologies can be used together:
QAQQINIQAQQINI is known as the query options table. It is a special table that DB2 for i5/OS uses to change/override the default behavior of the query engines (both CQE and SQE). Each system is shipped with a template QAQQINI file in the QSYS library. If this file is copied to the QUSRSYS library, its options are in effect for all users on the system. However, if you do not want its settings activated globally, you can copy the file to another library and use the CHGQRYA command to instruct the database to only use the settings for the file in that specific library and only for the job that issued the command.
One of the QAQQINI settings is IGNORE_DERIVED_INDEX. The default value of this setting is currently *NO, which means that the optimizer will not ignore derived indexes, so impacted queries must be processed by CQE. Changing this value to *YES allows SQE to process the query even when a derived key or select/omit index exists over a table in the query. When the query is run with this setting in place, SQE ignores the derived indexes over the referenced table and is allowed to process the request (barring any other SQE inhibitors).
Normally, you do not have to use QAQQINI as a database-tuning mechanism. Running without a QAQQINI file (which would be the same as running with the file using its default settings) provides good performance to the majority of System i customers. In fact, an important change planned for V6R1 is to change the default of the IGNORE_DERIVED_INDEX to *YES. As a result, all derived indexes and Select-Omit logical files will be ignored by default, and SQE processing can occur without intervention. But in the interim, you can use the technique described in this article.
CLI Connect Exit PointDB2 Web Query has three adapters that you can use to process a query request. When creating a new report, unless you need to access a multi-member file, a multi-format logical file, or an existing Query/400 object, the adapter you will be using is the DB2 CLI adapter. As described previously, when this adapter is used, the reporting server component of DB2 Web Query will generate an SQL statement to satisfy the request and submit the statement via a CLI request. These requests are handled by the QSQSRVR (pre-start) jobs on the System i. This is known as "server mode," and each time one of these jobs is used in this mode, a connection event occurs. The CLI connect exit point allows control to be passed to a user-written program each time one of these connection event occurs. By now, you probably see where this is going: Use this exit point to invoke a program each time a DB2 Web Query report is run. This program will direct the database to use a version of QAQQINI that instructs the database to ignore derived logical files, clearing the path for SQE processing.
By marrying these two technologies, we can allow other existing applications to run and continue to use Select-Omit logical files, while enabling DB2 Web Query requests to use SQE.
Determining Whether a Solution Is NeededBefore implementing the solution proposed in this article, you may want to first determine whether the described Select-Omit logical file and CQE scenario is occurring in your DB2 Web Query environment. Note: These same detection steps could be used for any SQL workload.
To do this, take the following steps:
STRDBMON OUTFILE(QGPL/CQE_TEST1) JOB(*ALL) FTRFILE(QZRE/*ALL) FTRUSER(COBB)
Note: You can also use iSeries Navigator to start a database monitor. This tool provides a graphical, wizard-like interface to start the monitor. For more information, consult the IBM Redbook OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4.
From an SQL interface (STRSQL or the Run SQL Scripts window in iSeries Navigator), analyze the data collected in the database monitor collection. The following SQL statement can be used for this type of analysis:
WITH STATEMENTS AS (
SELECT DISTINCT QQUCNT, QQJFLD, QQ1000
WHERE QQRID=1000 AND QQC21 IN ('OP','SI','UP','IN','DL') )
COUNT(*) AS COUNT,
B.QQ1000 AS STATEMENT
FROM cqe_test1 A, STATEMENTS B
WHERE QQRID = 3014
AND QQc16 ='N' AND A.QQJFLD=B.QQJFLD
GROUP BY a.qvc43, B.QQ1000
ORDER BY 2 DESC
After running this statement against the collected monitor data, look for rows where the QVC43 column equals DK. This value indicates that CQE processing occurred because "An index with derived key or select/omit was found over a queried table." Here is an example of how this would look:
(Click images to enlarge.)
For more information on the QVC43 column and the other types of information captured in the SQL performance monitor collection, see the online IBM publication DB2 Universal Database for iSeries Database Performance and Query Optimization, which can be downloaded from the i5/OS Information Center Web site.
In addition, you can use the iSeries Navigator tools to find and analyze the data collected in the database monitor and run the Visual Explain tool to see the optimization details of the captured query. In the example shown in Figure 1, notice that the reason SQE was not used is "Derived Key of Select/Omit Index Exists."
Figure 1: The Visual Explain tool allows you to see the optimization details of the captured query.
Note: The Visual Explain query engine feedback indicators require that you install the latest iSeries Access fixpack and Database Group PTFs.
Setting Up the EnvironmentOnce you have determined that the proposed solution is needed in your environment and you're armed with the knowledge of the technologies to be used, the next step is to perform the tasks necessary to implement it:
Since you want to ignore only derived indexes for DB2 Web Query requests, a global version of QAQQINI in the QUSRSYS library is undesirable. You want to activate this setting only for DB2 Web Query requests made through the CLI interface. To do that, you first need to create an "isolated" version of the query options file by following these steps:
|Last Updated on Tuesday, 08 January 2008 13:33|