Intel 170 Servers User Manual
y Statistical Strategies
y SMP Considerations
y Administration Examples (Adding a Partition, Dropping a Partition, etc.)
y SMP Considerations
y Administration Examples (Adding a Partition, Dropping a Partition, etc.)
Materialized Query Table Support
The initial release of i5/OS V5R3 includes the Materialized Query Table (MQT) (also referred to as
automatic summary tables or materialized views) support in UDB DB2 for i5/OS as essentially a
technology preview. Pre-April 2005 i5/OS V5R3 provides the capability of creating materialized query
tables, but no optimizer awareness of these MQTs. An April 2005 addition to DB2 for i5/OS V5R3 is
query optimizer support for recognizing and using MQTs. This additional support for recognizing and
using MQTs is limited to certain query functions. MQTs can provide performance enhancements in a
manner similar to indexes. This is done by precomputing and storing results of a query in the materialized
query table. The database engine can use these results instead of recomputing them for a user specified
query. The query optimizer will look for any applicable MQTs and can choose to implement the query
using a given MQT provided this is a faster implementation choice. For long running queries, the run time
may be substantially improved with judicious use of MQTs. For more information on MQTs including
how to enable this new support, for which queries support MQTs and how to create and use MQTs see
the DB2 for System i Database Performance and Query Optimization manual. For the latest information
on MQTs see
automatic summary tables or materialized views) support in UDB DB2 for i5/OS as essentially a
technology preview. Pre-April 2005 i5/OS V5R3 provides the capability of creating materialized query
tables, but no optimizer awareness of these MQTs. An April 2005 addition to DB2 for i5/OS V5R3 is
query optimizer support for recognizing and using MQTs. This additional support for recognizing and
using MQTs is limited to certain query functions. MQTs can provide performance enhancements in a
manner similar to indexes. This is done by precomputing and storing results of a query in the materialized
query table. The database engine can use these results instead of recomputing them for a user specified
query. The query optimizer will look for any applicable MQTs and can choose to implement the query
using a given MQT provided this is a faster implementation choice. For long running queries, the run time
may be substantially improved with judicious use of MQTs. For more information on MQTs including
how to enable this new support, for which queries support MQTs and how to create and use MQTs see
the DB2 for System i Database Performance and Query Optimization manual. For the latest information
on MQTs see
Fast Delete Support
As developers have moved from native I/O to embedded SQL, they often wonder why a Clear Physical
File Member (ClrPfm) command is faster than the SQL equivalent of DELETE FROM table. The reason
is that the SQL DELETE statement deletes a single row at a time. In i5/OS V5R3, DB2 for System i has
been enhanced with new techniques to speed up processing when every row in the table is deleted. If the
DELETE statement is not run under commitment control, then DB2 for System i will actually use the
ClrPfm operation underneath the covers. If the Delete is performed with commitment control, then DB2
FOR i5/OS can use a new method that’s faster than the old delete one row at a time approach. Note
however that not all DELETEs will use the new faster support. For example, delete triggers are still
processed the old way.
File Member (ClrPfm) command is faster than the SQL equivalent of DELETE FROM table. The reason
is that the SQL DELETE statement deletes a single row at a time. In i5/OS V5R3, DB2 for System i has
been enhanced with new techniques to speed up processing when every row in the table is deleted. If the
DELETE statement is not run under commitment control, then DB2 for System i will actually use the
ClrPfm operation underneath the covers. If the Delete is performed with commitment control, then DB2
FOR i5/OS can use a new method that’s faster than the old delete one row at a time approach. Note
however that not all DELETEs will use the new faster support. For example, delete triggers are still
processed the old way.
4.4 V5R2 Highlights - Introduction of the SQL Query Engine
In V5R2 major enhancements, entitled SQL Query Engine (SQE), were implemented in DB2 for i5/OS.
SQE encompasses changes made in the following areas:
y
SQE encompasses changes made in the following areas:
y
SQL query optimizer
y
SQL query engine
y
Database statistics
A subset of the read-only SQL queries are able to take advantage of these enhancements in V5R2.
SQE Optimizer
The SQL query optimizer has been enhanced with new optimization capabilities implemented in object
oriented technology. This object oriented framework implements new optimization techniques and
allows for future extendibility of the optimizer. Among the new capabilities of the optimizer are
enhanced query access plan costing. For queries which can take advantage of the SQE enhancements,
oriented technology. This object oriented framework implements new optimization techniques and
allows for future extendibility of the optimizer. Among the new capabilities of the optimizer are
enhanced query access plan costing. For queries which can take advantage of the SQE enhancements,
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
49