Intel AS/400 RISC Server, 170 Servers manual I5/OS V5R3 Highlights, I5/OS V5R3 SQE Query Coverage

Enhancements to extend the use of materialized query tables (MQTs) were added in i5/OS V5R4. New supported function in MQT queries by the MQT matching algorithm are unions and partitioned tables, along with limited support for scalar subselects, UDFs and user defined table functions, RCTE, and some scalar functions. Also new to i5/OS V5R4, the MQT matching algorithm now tries to match constants in the MQT with parameter markers or host variable values in the query. For more information on using MQTs see the DB2 for System i Database Performance and Query Optimization manual and the white paper, The creation and use of materialized query tables within IBM DB2 FOR i5/OS, available at http://www-304.ibm.com/jct09002c/partnerworld/wps/servlet/ContentHandler/SROY-6UZ5E6

The performance of queries which reference partitioned tables has been enhanced in i5/OS V5R4. The overhead when optimizing queries which reference a partitioned table has been reduced. Additionally, general improvements in plan quality have yielded run time improvements as well.

4.3 i5/OS V5R3 Highlights

In i5/OS V5R3, the SQL Query Engine (SQE) roll-out in DB2 for i5/OS took the next step. The new SQL Query Optimizer, SQL Query Engine and SQL Database Statistics were introduced in V5R2 with a limited set of queries being routed to SQE. In i5/OS V5R3 many more SQL queries are implemented in SQE. In addition, many performance enhancements were made to SQE in i5/OS V5R3 to decrease query runtime and to use System i resources more efficiently. Additional significant new features in this release are: table partitioning, the lookahead predicate generation (LPG) optimization technique for enhanced star-join support and a technology preview of materialized query tables. Also an April 2005 addition to the DB2 FOR i5/OS V5R3 support was query optimizer support for recognizing and using materialized query tables (MQTs) (also referred to as automatic summary tables or materialized views) for limited query functions. Two other improvements worth mentioning are faster delete support and SQE constraint awareness. This section contains a summary of the V5R3 information in the System i Performance Capabilities Reference i5/OS Version 5 Release 3 available at http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzahx/sc410607.pdf.

i5/OS V5R3 SQE Query Coverage

The query dispatcher controls whether an SQL query will be routed to SQE or to CQE (Classic Query Engine). The staged implementation of SQE enabled a very limited set of queries to be routed to SQE in V5R2. In general, read only single table queries with a limited set of attributes would be routed to SQE. The details of the query attributes for routing to SQE versus CQE in V5R2 are documented in the V5R2 redbook Preparing for and Tuning the V5R2 SQL Query Engine. With the V5R2 enabling PTF applied, PTF SI07650 documented in Info APAR II13486, the dispatcher routes many more queries through SQE. More single table queries and a limited set of multi-table queries are able to take advantage of the SQE enhancements. Queries with OR and IN predicates may be routed to SQE with the enabling PTF as will SQL queries with the appropriate attributes on systems with SMP enabled.

In i5/OS V5R3 a much larger set of queries are implemented in SQE including those with the enabling PTF on V5R2 and many queries with the following types of attributes:

y	Subqueries	y	Unions
y	Views	y	Updates
y	Common table expressions	y	Deletes

yDerived tables

IBM i 6.1 Performance Capabilities Reference - January/April/October 2008

© Copyright IBM Corp. 2008	Chapter 4 - DB2 Performance
	46