Intel 170 Servers DB2 for i5/OS Performance, New for i5/OS V6R1, I5/OS V6R1 SQE Query Coverage

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:

yNLSS/CCSID translation between columns

yUser-defined table functions

ySort sequence

yLateral correlation

yUPPER/LOWER functions

yUTF8/16 Normalization support (NORMALIZE_DATA INI option of *YES)

yLIKE with UTF8/UTF16 data

yCharacter 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:

yINSERT WITH VALUES statement or the target of an INSERT with subselect statement

yLogical files referenced in the FROM clause

yTables with Read Triggers

yRead-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