DB2 II: Performance Monitoring, Tuning and Capacity Planning Guide

Chapter 3. Key performance drivers of DB2 II V8.2 109

Distinct row values: No

Tablespace name:

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Source for statistics: Single Node

Prefetch page count: 32

Container extent page count: 32

Best practices

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

indexes.

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

routing.

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

efficient form.

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

invalid.

– 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.