50 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
CPU_RATIO and IO_RATIO specify how much faster or slower the data
source CPU and IO_RATIO speed is compared with the federated
server CPU speed and I/O rates, respectively. A low ratio indicates that
the data source workstation CPU (I/O) is faster than the federated
server workstation CPU (I/O). For low ratios, the optimizer will consider
pushing down operations that are CPU (I/O) intensive to the data
source. A low ratio is a value that is less than 1.
COMM_RATE specifies the speed of the communication network
between the data source and the federated server. A low
communication rate indicates slow network communication between
the federated server and the data source. Lower communication rates
encourage the query optimizer to reduce the number of messages and
amount of data sent to or from this data source. If the COMM_RATE
server option is set to a very small number, the optimizer produces a
query requiring minimal network traffic.
6. Remote SQL Generation relates to a set of steps that generate efficient SQL
statements based on the SQL dialect of the data source.
7. Code Generation (local query portions) is the final step during which the
compiler uses the access plan and the query graph model to create an
executable access plan, or section, for the local query portions. Information
about access plans for static SQL is stored in the system catalog tables.
When the package is executed, the database manager will use the
information stored in the system catalog tables to determine how to access
the data and provide results for the query.
3.3 Execution flow of a federated query
Very simply, the federated server distributes the query fragment assigned to each
data source to the corresponding wrappers, which in turn submits the query
fragment to the data source and retrieves the results. These steps are typical,
and can vary depending on how a specific source handles the concept of a
connection, whether the source accepts requests via a query language or
through some other API, what kind of result set cursors or iterators (if any) are
supported by a source, and so forth.
The typical process of distributing a query, executing it, and returning its results is
as follows:
1. The federated server passes authorization information to the wrapper and
requests it to establish a connection to the data source.
Chapter 3. Key performance drivers of DB2 II V8.2 51
2. The wrapper establishes the connection requested, and submits the query
fragment to the referenced data source. The wrapper then obtains an iterator
for the result set that the wrapper is to retrieve.
3. The federated server requests a row of results from the wrapper, which in
effect “forwards” the request to the data source.
4. The data source executes a portion of a query fragment in order to return the
requested row.
5. The wrapper retrieves the requested row, converts the types of the data in the
row to the federated server data types, and copies the converted types into
buffers. Results from data sources are retrieved in multi-row blocks if the
isolation level and parameters of the application permit this. So, results can be
retrieved from the data source to the federated server in blocks, and DB2 II
can send results to the application in multi-row blocks. The block size for both
is determined by the DB2 II database manager configuration parameter
RQRIOBLK. The default size is 32,767 bytes. The maximum size allowed is
65,535 bytes.
6. The federated server reads the data from the buffers and processes the data.
7. The federated server, the data source, and the wrapper repeat the three
previous steps for each successive row of results.
8. The wrapper retrieves the last row of the result set from the data source and
indicates this to the federated server.
9. If the same application references the same data source again, then the
connection to the data source will be reused. Otherwise, the connection to the
data source will be terminated when the application terminates its connection
to the federated server, or 100 commits have been processed without
reference to this data source.
10.The wrapper disconnects from the data source.
Figure 3-3 on page 52 is a pictorial representation of these steps, and highlights
some of the key options that influence performance in the execution flow.
Note: The federated server will reuse an existing connection if there
already exists an established connection within the same application to the
data source.

Get DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide now with O’Reilly online learning.

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