Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS

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