Intel 170 Servers User Manual
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
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
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.
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
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
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.
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:
PTF on V5R2 and many queries with the following types of attributes:
y
Derived tables
y
Deletes
y
Common table expressions
y
Updates
y
Views
y
Unions
y
Subqueries
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
46