24 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
2.3.4 Data access primitives
The basic function of SQE Data Access Primitives is to implement the query. Using the data
access methods derived from the object-oriented, tree-based architecture, it provides the
actual implementation plan of the query.
You may notice that the SQE Data Access Primitives use main memory and subsystem
resources more aggressively than with CQE. This is due to the fact that the SQE data access
algorithms are more in tune with OS/400 and i5/OS single-level storage. This more efficient
algorithm is the primary reason why the table scan access method is used more often on
SQE than with CQE.
For example, the mechanisms for storing temporary results were remodelled. Complex SQL
queries often combine multiple tables. This required DB2 Universal Database to combine all
of the tables into a temporary result table before the specified grouping or ordering could be
performed. By exploiting the efficient structures of the data access primitives and its close
proximity to the actual data, the design minimizes data movement and eliminates the need to
create real database objects to store temporary results. Instead of creating real database
objects, SQE employs expert cache to simultaneously retrieve data into memory pages and
process the data directly from these memory pages. This significantly reduces processing
overhead.
Although the SQE Data Access Primitives employ many of the same data access methods as
used in CQE, it underwent a total redesign. It features several data access methods and
algorithms for distinct processing and implements queries by fully using the symmetric
multiprocessing (SMP) feature. On average, CPU consumption is much less than what it was
previously. For a more detailed discussion about the data access primitives, refer to
Chapter 3, “Data access methods” on page 27.
2.3.5 The plan cache
The plan cache is a repository that contains query implementation plans for queries that are
optimized by the SQE Optimizer. Query access plans generated by CQE are not stored in the
plan cache. The architecture of DB2 for i5/OS allows for only one plan cache per System i
model or logical partition (LPAR).
The purpose of the plan cache is to facilitate the reuse of a query access plan at some future
stage when the same query, or a similar query, is executed. After an access plan is created, it
is available for use by all users and all queries, irrespective of the interface from which the
query originates. Furthermore, when an access plan is tuned, for example when creating an
index, all queries can benefit from this updated access plan. This eliminates the need to
re-optimize the query and results in greater efficiency faster processing time. In the case of
CQE, each query had to update its own access plan to benefit from the newly created index.
Figure 2-7 shows the concept of reusability of the query access plans stored in the plan
cache. The plan cache is interrogated each time a query is run to determine if a valid access
plan exists that satisfies the requirements of the query. If a valid access plan is found, it is
used to implement the query. Otherwise, a new access plan is created and stored in the plan
cache for future use. The plan cache is automatically updated when new query access plans
are created or when new statistics or indexes become available. Access plans generated by
CQE are not stored in the SQE Plan Cache.

Get Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.