Intel AS/400 RISC Server - Manual (Page 42)

Chapter 4. DB2 for i5/OS Performance
This chapter provides a summary of the new performance features of DB2 for i5/OS on V6R1, V5R4 and
V5R3, along with V5R2 highlights. Summaries of selected key topics on the performance of DB2 for
i5/OS are provided. General information and some recommendations for improving performance are
included along with links to the latest information on these topics. Also included is a section of
performance references for DB2 for i5/OS.
4.1 New for i5/OS V6R1
In i5/OS V6R1 there are several performance enhancements to DB2 for i5/OS. The evolution of the SQL
Query Engine (SQE), with this release, again supports more queries. Some of the new function supported
may also have a sizable effect on performance, including derived key indexes, decimal floating-point data
type, and select from insert. Lastly, modifications specifically to improve performance were made in
several key areas, including optimization improvements to produce more efficient access plans, reducing
full open and optimization time, and path length reduction of some basic, high use paths.
i5/OS V6R1 SQE Query Coverage
The query dispatcher controls whether an SQL query will be routed to SQE or to the Classic Query
Engine (CQE). SQL queries with the following attributes, which were routed to CQE in previous releases,
may now be routed to SQE in i5/OS V6R1:
y
NLSS/CCSID translation between columns
y
User-defined table functions
y Sort sequence
y Lateral correlation
y
UPPER/LOWER functions
y
UTF8/16 Normalization support (NORMALIZE_DATA INI option of *YES)
y
LIKE with UTF8/UTF16 data
y
Character based substring and length for UTF8/UTF16 data
Also, in V6R1, the default value for the QAQQINI option IGNORE_DERIVED_INDEX has changed
from *NO to *YES. The default behavior will now be to run supported queries through SQE even if
there is a select/omit logical file index created over any of the tables in the query. In V6R1 many types
of derived indexes are now supported by the SQE optimizer and usage of the QAQQINI option
IGNORE_DERIVED_INDEX only applies to select/omit logical file indexes.
SQL queries with the attributes listed above will be processed by the SQE optimizer and engine in V6R1.
Due to the robust SQE optimizer potentially choosing a better plan along with the more efficient query
engine processing, there is the potential for better performance with these queries than was experienced in
previous releases.
SQL queries which continue to be routed to CQE in i5/OS V6R1 have the following attributes:
y
INSERT WITH VALUES statement or the target of an INSERT with subselect statement
y
Logical files referenced in the FROM clause
y
Tables with Read Triggers
y
Read-only queries with more than 1000 dataspaces or updateable queries with more than 256
dataspaces.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008 Chapter 4 - DB2 Performance
42