Intel 7xx Servers, 170 Servers manual Like/Substring predicates DB2 Multisystem LOB columns

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

y	Sensitive cursor	y NLSS/CCSID translation between columns
y	Like/Substring predicates	y	DB2 Multisystem
y	LOB columns	y	ALWCPYDTA(*NO)
y References to DDS logical files		y Tables with select/omit logicals over them

i5/OS V5R3 SQE Performance Enhancements

Many enhancements were made in i5/OS V5R3 to enable faster query runtime and use less system resource. Highlights of these enhancements include the following:

yNew optimization techniques including Lookahead Predication Generation and Constraint Awareness

ySharing of temporary result sets across jobs

yReduction in size of temporary result sets

yMore efficient I/O for temporary result sets

yAbility to do some aggregates with EVI symbol table access only

yReduction in memory used during optimization

yReduction in DB structure memory usage

yMore efficient statistics generation during optimization

yGreater accuracy of statistics usage for optimization plan generation

The DB2 performance enhancements in i5/OS V5R3 substantially reduced the runtime of many queries. Performance improvements vary substantially due to many factors -- file size and layout, indexes and statistics available -- making generalization of performance expectations for a given query difficult. However, longer running queries which are newly routed to SQE in i5/OS V5R3, in general, have a greater likelihood of significant performance benefit.

For the short running queries, those that run less than 2 seconds, performance improvements are nominal. For subsecond queries there is little to no improvement for most queries. As the runtime increases, the reduction in runtime and CPU time become more substantial. In general, for short running queries there is less opportunity for improving performance. Also, the first execution of all the queries in these figures was measured so that a database open and full optimization were required. Database open and full optimization overhead may be higher with SQE, as it evaluates more information and examines more potential query implementation plans. As this overhead is much more expensive relative to actual query implementation for short running queries, performance benefits from SQE for the short running queries are minimized. However, in OLTP environments the plan caching and open data path (ODP) reuse design minimizes the number of opens and full optimizations needed. A very small percentage of queries in typical customer OLTP workloads go through full open and optimization.

The performance benefits are substantial for many of the medium to long running queries newly routed to SQE in i5/OS V5R3. Typically, the longer the runtime, the more potential for improvements. This is due to the optimizer constructing a more efficient access plan and the faster execution of the access plan with the SQE query engine. Many of the queries with runtimes greater than 2 seconds, especially those with runtimes greater than 10 seconds, reduced their runtime by a factor of 2 or more. Queries which run longer than 200 seconds were typically improved from 15% to over 100 times.

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

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