Intel AS/400 RISC Server V5R2 Highlights Introduction of the SQL Query Engine, SQE Optimizer

yStatistical Strategies

ySMP Considerations

yAdministration 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 http://www-1.ibm.com/servers/eserver/iseries/db2/mqt.html.

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.

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:

ySQL query optimizer

ySQL query engine

yDatabase 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,

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

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