Intel 170 Servers, AS/400 RISC Server, 7xx Servers manual I5/OS V5R4 SQE Query Coverage

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.

i5/OS V5R4 SQE Query Coverage

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	Sensitive cursor	y	LOB columns
y	Like/Substring predicates	y	ALWCPYDTA(*NO)

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

y	References to DDS logical files	y	DB2 Multisystem
y	NLSS/CCSID translation between columns	y	Tables with select/omit logicals over them

yUser-defined table unctions

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	Chapter 4 - DB2 Performance
	45