Chapter 3. Key performance drivers of DB2 II V8.2 109
Distinct row values: No
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
We recommend the following best practices for tuning data sources:
If there is a choice between creating nicknames for tables or for views, create
nicknames for tables.
Keep statistics of tables at data sources current.
Tune the data source for SQL statements coming from DB2 II, such as adding
3.4.4 Efficient SQL queries
In general, all the best practices associated with writing efficient SQL for
non-federated queries apply to federated environments as well. Refer to the
redbook DB2 UDB ESE V8 non-DPF Performance Guide for High Performance
OLTP and BI, SG24-6432; and the IBM DB2 UDB Administration Guide:
Performance Version 8.2, SC09-4821, for a complete discussion of this topic.
The redbook DB2 UDB’s High-Function Business Intelligence in e-business,
SG24-6546, provides guidelines on SQL coding practices to encourage MQT
The following additional considerations apply to creating nicknames and
federated queries. Best practices can be broadly classified as being nickname
related, query related, or miscellaneous.
Nickname-related best practices
In general, nickname-related best practices apply to DBAs as follows:
Ensure that nickname index definitions and statistics are current with respect
to the remote data source. This is by far the most significant factor that can
positively influence the performance of a federated query, since it would
facilitate the generation of a superior access plan.
Define informational referential integrity and functional dependency
constraints on nicknames if appropriate. This provides the DB2 optimizer with
additional information that allows it to consider rewriting the query into a more
110 DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide
When multiple tables at a single remote data source need to be joined in a
query, consider creating a view at the remote data source of such a join and a
nickname on this view. This facilitates simpler user queries as well as
pushdown in most cases.
The disadvantage of creating nicknames over remote views is that it hides the
details of the query from the optimizer and requires the DBA to manually
update the statistics in the global catalog since statistics are not captured
automatically on nickname creation on a view.
When defining the local data types for a nickname column that is joined with
columns of other nicknames/tables, ensure that the local data types of the
columns involved match perfectly in terms of scale and precision. Mismatched
data types may result in less pushdown, or the exclusion from consideration
of merge scan and hash joins for access path selection.
This may require modifying the default data type mapping provided by DB2 II
for a given data source.
If there is a mismatch in data type, precision/length, and scale between join
columns of two nicknames, the techniques to make them alike are:
– Alter the nickname to change the local type of the columns of one of the
nicknames to match the type, precision/length, and scale of the join
column of the other nicknames. This is only allowed if the new data type is
compatible with the existing data type. Also, this should not be done if it
will cause value truncation or padding that will cause join results to be
– Add a column to the table at one of the data sources with the new column
having the same type, length/precision, and scale as the join column in the
other data source. Update the new column with values from the former join
column in the same table. Create a unique index that includes the column
and update statistics for the table. Then drop and re-create the nickname
for the table and use the new column in the join.
– Create a view at one of the data sources in which the join column is cast
with the same type, length/precision, and scale as the join column in the
other data source. Create a new nickname for the view, but keep the old
Attention: If the data actually violates informational constraints that have
been defined, inconsistent results may be returned.
Attention: This approach is not generally recommended except in
extenuating circumstances such as pushdown not occurring with
nicknames on remote tables.