Chapter 2. DB2 Universal Database for iSeries performance basics 15
2.2.1 Database architecture before V5R2M0
For systems prior to the release of V5R2M0, all database requests are handled by the CQE.
Figure 2-3 shows a high-level overview of the architecture of DB2 Universal Database for
iSeries before OS/400 V5R2. The optimizer and database engine are implemented at
different layers of the operating system.
Figure 2-3 Database architecture before the release of V5R2M0: Classic Query Engine
Most CQE query decisions are made above the machine interface (MI) level. In CQE, the
interaction between the optimizer and the query execution component occurs across the MI,
resulting in interface-related performance overhead.
2.2.2 Current database architecture
With the release of V5R2M0, a new SQE was shipped. SQE and CQE coexist in the same
database environment. Depending on the database requests, the Query Dispatcher (see
2.2.3, “Query Dispatcher” on page 17) decides to route the query to either the CQE or SQE.
While both the new SQE and the existing CQE can handle queries from start to finish, the
redesigned engine simplifies and speeds up queries. In addition to providing the same
functionality as CQE, SQE also performs these functions:
򐂰 Moves the optimizer below the MI for more efficient query processing
򐂰 Separates and moves improved statistics to the Statistics Manager dashboard
򐂰 Uses an object-oriented design that accelerates the delivery of new database functionality
򐂰 Uses more flexible, independent data access options to provide autonomous query cruise
control
򐂰 Uses enhanced algorithms to provide greater responsiveness and query handling
򐂰 Provides enhanced performance on long-running complex query terrains
򐂰 Retains road maps to provide ease of use in query driving
򐂰 Provides additional and enhanced query feedback and debug information messages
through the Database Monitor and Visual Explain interfaces
DB2 Universal Database
(Data Storage and Management)
Optimizer
SQL
Extended
Dynamic
Prepare once
and then
reference
Dynamic
Prepare
everytime
Static
Compiled
embedded
statements
Host Server CLI/JDBC
Network
ODBC/JDBC/ADO/DRDA/XDA
Machine Interface (MI)
SLIC
The optimizer and
database engine
are separated at
different layers of
the operating
system
Native
(Record I/O)
16 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
There are several new and updated components of SQE in OS/400 V5R2 and i5/OS V5R3,
including:
򐂰 Query Dispatcher
򐂰 Statistics Manager
򐂰 SQE Optimizer
򐂰 Data Access Primitives
򐂰 Plan Cache
Figure 2-4 shows an overview of the DB2 Universal Database for iSeries architecture on
i5/OS V5R3 and where each SQE component fits. The functional separation of each SQE
component is clearly evident. In line with design objectives, this division of responsibility
enables IBM to more easily deliver functional enhancements to the individual components of
SQE, as and when required.
Figure 2-4 Current database architecture: Coexisting CQE and SQE
Note: Most of the SQE Optimizer components are implemented below the MI level, which
translates into enhanced performance.
SQL
Extended
Dynamic
Prepare once
and then
reference
Dynamic
Prepare
every time
Static
Compiled
embedded
statements
Host Server CLI/JDBC
Network
ODBC/JDBC/ADO/DRDA/XDA
Query Dispatcher
CQE Optimizer
SQE Optimizer
Query Optimizer
Machine Interface (MI)
DB2 Universal Database (Data Storage and Management)
SQL Statistics
Manager
SQE Optimizer
SQE Primitives
CQE
Database Engine
SLIC
The optimizer and
database engine
merged to form the
SQL Query Engine
and much of the
work was moved
to SLIC
Native
(Record I/O)

Get SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.