Maximize SQE usage with DDS-created DB2 for i databases

Boost your SQL performance

Applications utilizing SQL experience the best performance when processed by the DB2® for i SQL Query Engine (SQE). However, your use of SQE may be hampered if your database uses keyed logical files containing select/omit criteria. This article describes how you can use the IGNORE_DERIVED_INDEX query options attribute to maximize the use of the SQL Query Engine with databases that contain keyed logical files.

Kent Milligan (, Senior DB2 Consultant, IBM

Kent Milligan photoKent Milligan is a Senior DB2 for i consultant in IBM ISV Solutions Enablement for the IBM i platform. After graduating from the University of Iowa in 1989, Kent spent the first eight years of his IBM career as a member of the DB2 development team in Rochester. He speaks and writes regularly on various DB2 for i relational database topics.

14 January 2010

Also available in Portuguese

For many years, IBM i customers have reaped the benefits of a relational database that is integrated into the operating system — that database is now known as DB2 for i. One of the key benefits of the integrated DB2 for i database is its ability to support multiple interfaces.

DB2 for i features both an SQL interface and a native (non-SQL) interface. These two interfaces can be used interchangeably. DB2 objects created with the native DDS (Data Definition Specifications) interface can be accessed and changed by applications using SQL. In a similar manner, applications using the native record-level interface can write and read data from DB2 objects created with SQL. This incredible flexibility provided by the integrated DB2 for i database has enabled IBM i applications to evolve over time and leverage new SQL technologies such as OLAP expressions and Sequence objects without developers having to recreate all of their databases.

The DB2 for i SQL Query Engine (SQE) has been evolving in a similar way to those IBM i applications that are leveraging SQL enhancements. The first stage of SQE was delivered way back with the V5R2 release to improve the performance of the SQL data access interfaces. The capabilities and performance of SQE has dramatically improved in every new release since V5R2. Some IBM i customers have seen the performance of complex queries improve over 10x with SQE.

In addition to performance improvements, the other good news is that the list of SQL features that SQE does not support continues to shrink. As of DB2 for i version 6.1, the only unsupported features are:

  • Logical file reference on the FROM clause
  • Keyed select/omit logical files defined on the referenced table or physical file
  • ICU 2.6.1 sort sequences
  • Non-SQL interfaces (OPNQRYF, Query/400, QQQQRY API)

DB2 for i cannot use SQE to process an SQL statement if the SQL request or application environment contains one of the unsupported features. For links to background information on SQE, refer to the Resources section of this article.

SQE support for logical files

While the SQL Query Engine does support some DDS-created DB2 objects, the unsupported features list demonstrates that not all DDS objects are supported. SQE can process SQL statements that reference physical files. Furthermore, the SQE query optimizer can use keyed logical files to speed up the sorting or selection of data during execution of an SQL statement.

The initial SQE optimizer support for keyed logical files, however, was limited to simple keyed logical files that are equivalent to an SQL index. Whenever the SQE query optimizer encountered a keyed logical file with derivations such as field mapping or select/omit criteria, the associated SQL statement was rerouted to the Classic Query Engine (CQE) for execution.

With the DB2 for i 6.1 release, the SQE query optimizer only has to pass the SQL request to CQE for keyed select/omit logical files — at the 6.1 release level, the SQE optimizer supports keyed logical files that contain derived key field mapping. Examples of key field mapping include mapping a key field to a different length or Coded Character Set Identifier (CCSID) setting.

SQE logical file support example

To make sure the concept of SQE logical file support is understood, let's review a simple scenario. Suppose you have a physical file (MYPF) that has a keyed logical file (LF2) defined over it. As shown in Listing 1, the DDS source for LF2 contains select/omit criteria for the field name REGNFLD.

Listing 1. DDS source for logical file LF2
*************** Beginning of data *************************************
                R PFREC                     PFILE(MYPF)                
                  CTYNAME       30A                                    
                K CTYID                                                
                S REGNFLD                   COMP(EQ 'EAST')            
****************** End of data ****************************************

The relationship between LF2 and MYPF is portrayed graphically in Figure 1. In addition to LF2, the MYPF file has two other indexes defined over it: an SQL index named IX1 and a simple keyed logical file named LF1. The select/omit criteria contained in LF2 causes DB2 to classify it as a derived index. In this example, LF2 is the only index that the query optimizer would classify as a derived index.

Figure 1. Index and logical file relationships to the MYPF physical file
Hierarchy of the MYPF indexes and logical files. Programs using the native interface access the LF2 keyed logical file.

For our scenario, the sample SQL SELECT statement shown in Listing 2 is included in an application to retrieve data from MYPF.

Listing 2. Sample SELECT statement
  SELECT ctyname, regnfld FROM mypf 
   WHERE ctyname='ROCHESTER'

When the application is run, the SQE query optimizer gets first chance at processing this SQL statement. One of the first tasks the SQE optimizer performs is to examine the indexes over the MYPF file to determine if any of the indexes can be used to speed up performance of the query. When the SQE optimizer detects that one of the indexes contains the select/omit derivation, it immediately stops processing and passes execution of the SELECT statement to CQE.

SQE decision making process

As described in the previous section, the mere association of a keyed select/omit logical file prevents SQE from processing any SQL statement that references the MYPF logical file. This SQE decision-making process is depicted below in Figure 2.

Figure 2. Derived logical file preventing SQE usage
MYPF hierarchy showing SQL requests not being able to use SQE because of the select/omit criteria in LF2.

The SQL Query Engine's lack of support for derived keyed logical files can be problematic because it is common for IBM i applications to have an interface mixture similar to that shown in Figure 2 — a set of DB2 objects originally created from the DDS interface now being accessed by programs using the native record-level interface as well as SQL interfaces.

Enabling SQE usage in DDS environments

Obviously, IBM would not suggest that customers delete their derived keyed logical files just to enable SQE to be used more often on SQL requests. This of course would break any programs using these same logical files. Therefore, IBM created a new option for the QAQQINI query options file to enable SQE to be used in mixed environments without requiring the deletion of any logical files.

The name of the new QAQQINI option is IGNORE_DERIVED_INDEX. This option was first introduced in the V5R3 release of DB2. By specifying a value of *YES for this option, you cause the SQE query optimizer to ignore any derived keyed logical files that it encounters during the optimization process. When creating the query plan to run the associated SQL statement, the SQE optimizer uses just the SQL indexes or non-derived keyed logical files for the DB2 table. The SQE query optimizer is not used by programs using the native record-level interface, so the IGNORE_DERIVED_INDEX option has no effect on programs that use a derived keyed logical file.

The impact of this QAQQINI option is represented graphically in Figure 3. Specifying a value of *YES for the IGNORE_DERIVED_INDEX option effectively removes all derived key logical files from the list of indexes considered by the SQE optimizer. Therefore, setting the IGNORE_DERIVED_INDEX option to *YES assumes there are enough SQL indexes and non-derived logical files for the optimizer to use in the runtime execution of the SQL statement.

Figure 3. QAQQINI option IGNORE_DERIVED_INDEX = *YES enabling SQE usage
SQL requests are now able to use SQE because IGNORE_DERIVED_INDEX is set to *YES and the select/omit criteria in LF2 is ignored

If a database has a good indexing strategy, performance is usually better with SQE running an SQL statement without the use of a derived key logical file than it is with CQE using a derived key logical file in the runtime implementation. However, there are cases where a CQE query plan that utilizes keyed select/omit logical files has performed better than the SQE implementation. As a result, it is a good idea to plan on doing some performance testing and analysis of your SQL applications before switching your production systems to use the IGNORE_DERIVED_INDEX option.

Default value of IGNORE_DERIVED_INDEX changed in IBM i 6.1

Because SQL performance using SQE is typically expected to be better, IBM has changed the default value of the IGNORE_DERIVED_INDEX option from *NO to *YES in the IBM i 6.1 release. So with DB2 for i 6.1, if an SQL statement references a physical file associated with a keyed select/omit logical file, SQE will process the SQL request without you needing to explicitly set the IGNORE_DERIVED_INDEX option in the QAQQINI query options file.

This change to the default setting of IGNORE_DERIVED_INDEX also means you should do some performance testing before and after upgrading to IBM i 6.1 if your environment has applications that use SQL against databases that contain keyed select/omit logical files. Performance testing and analysis may uncover some rare instances where response times for SQL statements would be faster using a CQE query plan that features a keyed select/omit logical file. In these rare situations, you may need to selectively set applications to use a value of *NO for the IGNORE_DERIVED_INDEX option with IBM i 6.1.

SQL derived index support

The DB2 for i 6.1 release also includes support for derived SQL indexes. The value of the IGNORE_DERIVED_INDEX option has no impact on the SQE query optimizer's usage of SQL derived indexes. In fact, only the SQE query optimizer has the ability to use the new derived SQL indexes in query plans — yet another reason why you want as many SQL statements as possible being processed by the SQL Query Engine.

Listing 3. SQL derived index examples
CREATE INDEX ix_uCompName ON cust(UPPER(company_name))

CREATE INDEX ix_FullName ON emp(CONCAT(CONCAT(FName,' '), LName))

Setting up and using a QAQQINI option file

Even if you are a long time user of DB2 for i, there's a good chance that you have never had a need to use a QAQQINI options file to override the default settings of the DB2 for i query optimizer and engine. So here are the required steps for using the QAQQINI file to set the IGNORE_DERIVED_INDEX option to enable more usage of the SQL Query Engine.

Step 1. Create a QAQQINI option file

Use the CRTDUPOBJ (Create Duplicate Object) system command to create your own QAQQINI file from the master template that is shipped with each system in the QSYS library. The example in Listing 4 shows the CRTDUPOBJ command being used to create a copy of the QAQQINI file in the library named MYLIB.

Listing 4. CRTDUPOBJ command example
                      TOLIB(MYLIB) DATA(*YES) TRG(*YES)

You have to use the CRTDUPOBJ command because there are IBM-supplied triggers associated with the QAQQINI file. You must specify *YES for the TRG (Trigger) parameter to ensure that these triggers are propagated to your copy of the QAQQINI file.

Also notice that the CRTDUPOBJ command keeps the same name of QAQQINI for the target file. A file name of QAQQINI is required in order for DB2 to recognize that the file is a query options files.

The final parameter to pay attention to on the CRTDUPOBJ command is TOLIB. This parameter controls which library you create the QAQQINI file in. In the example in Listing 4, the file is created in the library named MYLIB.

Step 2. Set values in the QAQQINI option file

Next you need to specify a value for the IGNORE_DERIVED_INDEX option. Because the QAQQINI file itself is just another DB2 table, you can use any database interface to assign a value for IGNORE_DERIVED_INDEX. One of the simplest ways to change the value is to use an SQL UPDATE statement such as the one shown in Listing 5.

Listing 5. QAQQINI update example

Step 3. Scope the usage of the QAQQINI option file

The final step is to tell DB2 for i where to find your QAQQINI file. It's possible to accomplish this on the first step. By default, DB2 looks for a QAQQINI file in the QUSRSYS library. Therefore, if you create your QAQQINI file in the QUSRSYS library, the query option values contained in the QAQQINI file will impact all applications and users on the system. If a query options file is not found in QUSRSYS, then DB2 for i uses the default values for all of the query options.

You can use the CHGQRYA (Change Query Attributes) command to scope the effect of your QAQQINI file to a specific application or job. Instead of looking for a QAQQINI file in the QUSRSYS library, DB2 for i uses the QAQQINI file in the library you specify on the QRYOPTLIB (Query Options Library) parameter of the command.

Listing 6 shows a sample CHGQRYA command that redirects DB2 for i to use the QAQQINI file in the MYLIB library instead of the default QUSRSYS library.

Listing 6. CHGQRYA command example

The QAQQINI file location specified on a CHGQRYA command remains in effect for the duration of the job or user session, or until the QRYOPTLIB parameter is changed by another CHGQRYA command.

For your applications that use industry standard interfaces such as ODBC and JDBC, the database middleware provided with the IBM i operating system includes support for a QAQQINI connection property. When this connection property is specified, the IBM drivers execute the CHGQRYA command on behalf of the application. Table 1 shows the connection property used by each supported interface.

Table 1. IBM i middleware support for the QAQQINI connection property
InterfaceConnection property
IBM Toolbox for Java JDBC Driverqaqqinilib
IBM Developer Kit for Java Native JDBC Driverqaqqinilib
IBM System i Access for Windows ODBC DriverQAQQINILIB or QAQQINILibrary
IBM System i Access for Windows .NET ProviderQueryOptionsFileLibrary
IBM System i Access for Windows OLE DB Provider'Query Options File Library'

QAQQINI file override support

Another new feature provided in the DB2 for i 6.1 release is the OVERRIDE_QAQQINI system stored procedure in the QSYS2 library. This enables you to use stored procedure calls to control QAQQINI options such as IGNORE_DERIVED_INDEX, instead of working directly with a QAQQINI options file. This is especially useful when you want to temporarily modify an existing QAQQINI options file for the execution of one or two SQL statements.

The OVERRIDE_QAQQINI procedure supports the following three parameters:

  • Function code
    • 1 = Create a temporary QAQQINI file in QTEMP library based on the contents of the currently active QAQQINI file. Invoke the CHGQRYA command to specify usage of the new QAQQINI file in the QTEMP library.
    • 2 = Update the temporary QAQQINI file with the supplied QAQQINI option.
    • 3 = Discard the temporary QAQQINI file.
  • QAQQINI option name
  • QAQQINI option value

The OVERRIDE_QAQQINI procedure only applies the specified query options to the job (or connection) that invokes the stored procedure because the temporary query options file is located in the QTEMP library.

As you might imagine from the three function code values supported by the OVERRIDE_QAQQINI procedure, usage of the override stored procedure is normally a three step process. Listing 7 shows an example of how to use this process.

Listing 7. OVERRIDE_QAQQINI example
/* Create, activate, and populate temporary query options file */
CALL qsys2/override_qaqqini(1, '', '');
CALL qsys2/override_qaqqini(2, 'IGNORE_DERIVED_INDEX', '*YES');
/* Run SQL statement with IGNORE_DERIVED_INDEX option set to *YES */
SELECT ctyname, regnfld FROM mypf WHERE ctyname='ROCHESTER' ;
/* Discard temporary query options file */
CALL qsys2/override_qaqqini(3, '', '');

In the above example, you want the application to run only a single SELECT statement with the IGNORE_DERIVED_INDEX option set to *YES. Before running the SQL request, you make two calls to the OVERRIDE_QAQQINI procedure. The first call uses function code 1 to create and activate a temporary QAQQINI options file. The second call uses function code 2 to change the IGNORE_DERIVED_INDEX option to *YES in the temporary options file. After the SELECT statement is executed, the OVERRIDE_QAQQINI procedure is called a final time with function code 3 to deactivate and remove the temporary QAQQINI options file.


With a firm understanding of the QAQQINI query option file's IGNORE_DERIVED_INDEX option, you now have the ability to boost your SQL application performance by maximizing usage of the SQL Query Engine — even when your database contains keyed logical files with derivations!





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

Zone=Information Management, IBM i
ArticleTitle=Maximize SQE usage with DDS-created DB2 for i databases