Chapter 2. The architecture of DB2 for i5/OS 13
2.2 Query processing before OS/400 V5R2
To fully understand the implementation of query management and processing in DB2 for
i5/OS starting with OS/400 V5R2, it is important to see how the queries were implemented in
previous releases of OS/400 for DB2 Universal Database for iSeries.
Figure 2-1 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. The interaction between the optimizer and the
database engine occurs across the Machine Interface (MI). This results in a definite
performance overhead. This is one of the areas that was targeted for improvement during the
re-engineering process of DB2 Universal Database for iSeries for OS/400 V5R2.
Figure 2-1 Database architecture before OS/400 V5R2
2.3 Query processing starting with OS/400 V5R2
There are several new and updated components of SQE starting from OS/400 V5R2. The
SQE components include:
򐂰 Query Dispatcher
򐂰 SQE Optimizer
򐂰 Statistics Manager
򐂰 SQE Data Access Primitives
򐂰 Plan cache
In the following section, we look briefly at the underlying architecture that supports SQE in
OS/400 starting with V5R2. We follow this with a detailed discussion of the SQE components.
SLIC
Machine Interface (MI)
DB2 Universal Database
(Data Storage and Management)
Optimizer
Static
Compiled
embedded
statements
Dynamic
Prepare
every
time
Extended
Dynamic
Prepare once and
then reference
SQL
Host Server
CLI/JDBC
ODBC/JDBC/ADO/DRDA/XDA
Network
Native
(Record I/O)
14 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
The architecture
Figure 2-2 shows an overview of the DB2 for i5/OS architecture starting with OS/400 V5R2
and where each SQE component fits. The functional separation of each SQE component is
clearly evident. In line with our design objectives, this division of responsibility enables IBM to
more easily deliver functional enhancements to the individual components of SQE, as and
when required.
Notice that most of the SQE Optimizer components are implemented below the MI. This
translates into enhanced performance efficiency.
Figure 2-2 Database architecture starting with OS/400 V5R2
SLIC
Machine Interface (MI)
CQE Database Engine
Static
Compiled
embedded
statements
Dynamic
Prepare
every
time
Extended
Dynamic
Prepare once and
then reference
SQL
Host Server
CLI/JDBC
ODBC/JDBC/ADO/DRDA/XDA
Network
Native
(Record I/O)
Optimizer
DB2 Universal Database (Data Storage and Management)
SQE Optimizer
SQE Data Access Primitives
SQE Statistics
Manager
CQE Database Engine
CQE Optimizer
SQE Optimizer

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.