![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
In every operating system release since V5R2, DB2 for IBM i has been transitioning more queries from the Classic Query Engine (CQE) to the SQL Query Engine (SQE). For IBM i 6.1, the theme of SQE enhancement continues with the addition of the following SQE support:
- National Language Sort Sequence (NLSS) support
- CCSID translation support including UPPER and LOWER functions
- CASE enhancements
- User Defined Table Function (UDTF) support
- Lateral Correlation
This article explains how to collect an SQL Performance Monitor and understand how many of the SQL queries are using SQE and why other SQL statements continue to use CQE. SQE is the strategic query engine on DB2 for i, with better performance, scaling and tooling such as the SQL Plan Cache.
SQL Performance Monitors can be collected and analyzed from System i Navigator. An alternative approach is to use the Start Database Monitor (STRDBMON) and End Database Monitor (ENDDBMON) commands to collect the data and craft your own queries to analyze the resulting monitor output file. This article focuses on the System i Navigator approach to data collection and analysis. For either approach, *JOBCTL user special authority is needed to collect the data because the example collects monitor detail over all jobs. If the SQL Performance Monitor target job was a specific job and that job was started by the current user, *JOBCTL user special authority is not required.
From System i Navigator, expand the databases section for the machine, right-click on SQL Performance Monitors and select New.
Name the new monitor and select a schema (library) for the data.
Choose between the many SQL Performance Monitor pre-filters. The pre-filters limit the amount of data collected, which is an important performance and monitor output size consideration. For my example, I chose a minimum estimate query runtime of 0 because I wanted to avoid collecting detail for non-query SQL statements.
For the sake of my example, I monitored across all jobs (existing and future) on the entire system.
A confirmation screen indicates we're ready to activate the monitor.
The new monitor is automatically added to the list of SQL Performance Monitors. When the monitor state is started, SQL statements which match the pre-filter criteria for the monitored jobs will be collected. A right click on the monitor shows the options available to the user. The monitor can then be analyzed while it's running or after it has been ended (turned off).
A public monitor such as the one shown in this example could collect a great deal of data on a busy machine. Take care in deciding the time and duration for the SQL performance monitor.
When the Analyze option is chosen, you will see an overview screen. The overview contains useful summary information for the monitor, including an indication of the number of queries that used SQE and CQE. One analysis approach is to select the Summary option for CQE queries. This is going to help you understand why those queries used CQE.
The default view is to see every monitor column. My suggestion is to use the Columns option under the View pulldown to limit and order the columns. For large monitors, this technique will improve the System i Navigator performance. For any monitor, this technique will make it easier to analyze the data.
For this exercise, I chose Operation to see the command or SQL statement name, along with the CQE Reason. The usage count and average runtime will provide me with the necessary context to understand the performance impact of the queries.
The example data shows that some queries are using a non-SQL interfaces (RUNQRY command, OPNQRYF command and the QQQQRY() API while the other instance of CQE use is tied to an INSERT with VALUES statement. The INSERT with VALUES is a special case because it would not benefit from executing through SQE because the statement lacks selection criteria, grouping or ordering.
The overview information helps bound how important this CQE versus SQE use question is to your environment or application. Once you decide that some CQE use analysis is warranted, the different reasons for CQE use will dictate whether any actions can be taken to shift more query engine work to SQE.
Table 1 lists the remaining reasons why CQE would be used on IBM i 6.1. The center column shows the values you would observe when analyzing the SQL Performance monitor and the last column defines the values which appear within the QVC43 column of the output monitor file. The QVC43 value would only be interesting to those working directly with the monitor output file instead of using the analysis feature within System i Navigator.
Table 1.
| CQE Reason | SQL Performance Monitor value | QVC43 column value (when QQRID=3014) |
|---|---|---|
| Simple insert | INSERT_VALUES_OR_INSERT_TARGET | 'IO' |
| Too many tables in the query | GREATER_THAN_256_TABLES | 'NF' |
| Not an SQL query or the query was not executed via an SQL interface | NON_SQL_INTERFACE | 'NS' |
| There was a distributed table in the query | DISTRIBUTED_TABLE | 'DF' |
| A read trigger was defined on the queried table | READ_TRIGGER | 'RT' |
| There was a program described file in the query | IDDU_LINKED_DEFINITION | 'PD' |
| WHERE CURRENT OF was used on a partitioned table | COMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE | 'WC' |
| WHERE CURRENT OF was used on a partitioned table | COMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE | 'WC' |
| A DDS logical file was specified in the query definition | DDS_LOGICAL_FILE | 'LF' |
| A DDS logical file with a derived key or select/omit criteria was found over a queried table | DERIVED_KEY_OR_SELECT_OMIT_LOGICAL_FILE | 'DK' |
The last reason in the table is special because there is an operational control to allow SQE to be used even when a logical file containing a derived key or select/omit criteria exists over a table. More information about this control can be found in the Maximize SQE usage with DDS-created DB2 databases article.
A recent enhancement to DB2 for i 6.1 makes it possible to understand the optimizer use at the SQL statement level within the SQL Performance Monitor. The example scenario above depends on this enhancement. For those who prefer to work directly with the database monitor output file, you'll need the detail from this section.
The QQSMINT5 column within the 1000 record (QQRID=1000) represents the optimizer use. There are five possible values which can appear in this column.
- null = The database monitor file predates this enhancement
- 0 = Does not apply for this statement because neither query engine was needed or used
- 1 = SQE was used (SQL Query Engine)
- 2 = CQE was used (Classic Query Engine)
- 3 = CQE direct was used (statements like INSERT WITH VALUES)
The QQSMINT5 value is accurate for active jobs, even those which are reusing Open Data Paths (ODPs) and SQL cursors. To understand the reason for CQE use, you will need to examine the associated 3014 record. The 3014 record is generated during full open of the SQL cursor, which explains why the CQE reason could be obscured for SQL statements in reuse mode.
The following V6R1 PTFs must be loaded in order to use this improvement to the QQSMINT5 field.
- SI33874
- SI33875
- SI33876
- SI34001
- SI34002
After moving to IBM i 6.1, take time to gauge your use of CQE and SQE. DB2 for i investment is focused firmly behind SQE and you should be able to observe expanded use SQE on 6.1. Some installations may have existing QAQQINI query file options configured with IGNORE_DERIVED_INDEX with '*NO'. Use the enhancements and tooling described in this article to understand the scope of CQE activity and consider removing any impediments to SQE usage.
