Intel AS/400 RISC Server ユーザーズマニュアル

Table Expressions (RCTE) which allow for more elegant and better performing implementations of
recursive processing.  In addition, enhancements have been made in i5/OS V5R4 to the support for
materialize query tables (MQTs) and  partitioned table processing, which were both new in i5/OS V5R3. 

The query dispatcher controls whether an SQL query will be routed to SQE or to CQE . SQL queries with
the following attributes, which were routed to CQE in previous releases, may now be routed to SQE in
i5/OS V5R4:

y

ALWCPYDTA(*NO)

y

Like/Substring predicates

y

LOB columns

y

Sensitive cursor

SQL queries which continue to be routed to CQE in i5/OS V5R4 have the following attributes:

y

User-defined table unctions

y

Tables with select/omit logicals over them

y

NLSS/CCSID translation between columns

y

DB2 Multisystem

y

References to DDS logical files

In general, queries with Like and Substring predicates which are newly routed to SQE see substantial
performance improvements in i5/OS V5R4.  For a group of widely varying queries and data, including a
wide range of  Like and Substring predicates and various file sizes, a large percentage of the queries saw
up to a 10X reduction in query run time.  Queries with references to LOB columns, which were newly
routed to SQE,, in general, also experience substantial performance improvements in i5/OS V5R4.  For a
set of queries which have references to LOB columns, in which the queries and data vary greatly a large
percentage ran up to a 5X faster. .

A new addition to SQE is the creation and use of temporary indexes.  These indexes will be

 

created

because they are  required for implementing certain types of query requests or because they allow for
better performance.

  

The implementation of queries which require live data may require temporary

indexes, for example, queries that run with a sensitive cursor or with ALWCPYDTA(*NO).  In the case
of using a temporary index for better performance, the SQE optimizer costs the creation and use of
temporary indexes during plan optimization. An access plan will choose a temporary index if the
amortized

 

cost of building the index, provided one does not exist, reduces the query run time of the access

plan enough that this plan wins over other plan options. The temporary indexes that the optimizer
considers building are the same indexes in the ‘index advised’ list for a given query.  Features unique to
SQE temporary indexes, compared to CQE temporary indexes, are the longer lifetimes and higher degree
of sharing of these indexes. SQE temporary indexes may be reused by the same query or other queries in
the same job or in other jobs. The SQE temporary indexes will persist and will be maintained until the last
query which references the temporary index is hard closed and the plan is removed from the plan cache.
In many cases, this means the temporary indexes will persist until the last job which was using the index
is ended.  The high degree of sharing and longer lifetime allow for more reuse of the indexes without
repeated create index cost.

New function for implementing applications that work with recursive data has been added to i5/OS
V5R4.  Recursive Common Table Expressions (RCTE) and Recursive Views may now be used in these
types of applications, versus using SQL Stored Procedures and temporary results tables.  For more
information on using RCTEs and Recursive Views see the DB2 for System i Database Performance and
Query Optimization manual. 

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

©

 Copyright IBM Corp. 2008