![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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
FROMclause - 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.
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
CTYID
REGNFLD
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
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.
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
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
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.
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
CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE)
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
UPDATE MyLib/QAQQINI
SET QQVAL = '*YES'
WHERE QQPARM = 'IGNORE_DERIVED_INDEX'
|
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 exampleCHGQRYA QRYOPTLIB(MYLIB) |
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
| Interface | Connection property |
|---|---|
| IBM Toolbox for Java JDBC Driver | qaqqinilib |
| IBM Developer Kit for Java Native JDBC Driver | qaqqinilib |
| IBM System i Access for Windows ODBC Driver | QAQQINILIB or QAQQINILibrary |
| IBM System i Access for Windows .NET Provider | QueryOptionsFileLibrary |
| IBM System i Access for Windows OLE DB Provider | 'Query Options File Library' |
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 theCHGQRYAcommand 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!
Learn
-
Visit the
DB2 for i SQL Query Engine Web site to read about the evolution of the SQL Query Engine and it's current status.
- "IBM DB2
for i 6.1 -- Sophistication simplified" describes the SQL Query Engine enhancements delivered with the IBM DB2 for i 6.1 release.
- "Gauge
SQE use in DB2 for i 6.1" shows you how to collect SQL Performance Monitors to better track SQE usage in the DB2 for i 6.1 release.
-
Attend the
DB2 for i SQL
Performance Workshops
to understand the science of query optimization, the art of
query optimization, SQL performance techniques and considerations, and SQL
performance tools and analytical methods.
Discuss
- Participate in the discussion forum.
- Get involved in the
IBM DB2 Web Query community.
Kent 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.
