This article explains how to collect a 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.

Share:

Scott L. Forstie (forstie@us.ibm.com), Senior Software Engineer, IBM

Scott Forstie is a senior software engineer at IBM, and he is the SQL development leader for DB2 for IBM i in Rochester, MN. Before working on DB2, he worked on UNIX® enablement for the AS/400® and S/390® systems.



22 November 2009

Also available in Chinese Portuguese

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.

Collecting the data

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.

Figure 1.
System i Navigator

Name the new monitor and select a schema (library) for the data.

Figure 2.
SQL Performance Monitor Wizard

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.

Figure 3.
SQL Performance Monitor Wizard

For the sake of my example, I monitored across all jobs (existing and future) on the entire system.

Figure 4.
SQL Performance Monitor Wizard

A confirmation screen indicates we're ready to activate the monitor.

Figure 5.
SQL Performance Monitor Wizard

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).


User Beware

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.

Figure 6.
System i Navigator

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.

Figure 7.
Query Engine Use

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.

Figure 8.
Query Engine Use

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.

Figure 9.
Select Columns

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.

Figure 10.
Query Engine Use

Reasons for CQE use

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 ReasonSQL Performance Monitor valueQVC43 column value (when QQRID=3014)
Simple insertINSERT_VALUES_OR_INSERT_TARGET'IO'
Too many tables in the queryGREATER_THAN_256_TABLES'NF'
Not an SQL query or the query was not executed via an SQL interfaceNON_SQL_INTERFACE'NS'
There was a distributed table in the queryDISTRIBUTED_TABLE'DF'
A read trigger was defined on the queried tableREAD_TRIGGER'RT'
There was a program described file in the queryIDDU_LINKED_DEFINITION'PD'
WHERE CURRENT OF was used on a partitioned tableCOMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE'WC'
WHERE CURRENT OF was used on a partitioned tableCOMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE'WC'
A DDS logical file was specified in the query definitionDDS_LOGICAL_FILE'LF'
A DDS logical file with a derived key or select/omit criteria was found over a queried tableDERIVED_KEY_OR_SELECT_OMIT_LOGICAL_FILE'DK'

One special case

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.


How the counting happens

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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=449245
ArticleTitle=Gauge SQE use in DB2 for i 6.1
publish-date=11222009