The following sections contain nickname-specific factors that can affect global optimization.
DB2 can use information about indexes at data sources to optimize queries. For this reason, it is important that the index information available to DB2 be current. The index information for nicknames is initially acquired at the time the nickname is created. Index information is not gathered for nickname on objects that do not have indexes such as views.
Creating Index Specifications on Nicknames
You can create an index specification for a nickname. Index specifications build an index definition (not an actual index) in the catalog for use by the DB2 optimizer. Use the CREATE INDEX SPECIFICATION ONLY statement to create index specifications.
Consider creating index specifications when:
Consider your needs before issuing CREATE INDEX statements against a nickname for a view. In one case, if the view is a simple SELECT on a table with an index, creating indexes on the nickname (locally) that match the indexes on the table at the data source can significantly improve query performance. However, if indexes are created locally over views that are not simple select statements (for example, a view created by joining two tables), query performance may suffer. For example, if an index is created over a view that is a join of two tables, the optimizer may choose that view as the inner element in a nested loop join. The query will have poor performance because the join will be evaluated several times. An alternative is to create nicknames for each of the tables referenced in the data source view and create a local view at DB2 that references both nicknames.
Catalog Statistics Considerations
Catalog statistics describe the overall size of nicknames and the range of values in associated columns . They are used by the optimizer when calculating the least-cost path for processing queries containing nicknames. Nickname statistics are stored in the same catalog views as table statistics. Although DB2 can retrieve the statistical data held at a data source, it cannot automatically detect updates to existing statistical data at data sources.
Furthermore, DB2 has no mechanism for handling object definition or structural changes (adding a column) to objects at data sources. If the statistical data or structural data for an object has changed, you have two choices:
By doing this, you will ensure that Federated System knows about all indexes on remote objects.
To check nickname statistics in SYSSTAT.COLUMNS:
select char(colname,20) as colname, colcard, char(high2key, 15) as high, char(low2key, 15) as low from sysstat.columns where tabschema = 'DNTNCK' and tabname = 'ACCOUNT_XREF';
The results may be blank; if so, you can supply values based on knowledge or remote source statistics.
update sysstat.columns set colcard=1000, high2key = '1999-12-31', low2key = '1995-01-01' where colname = 'ACCT_NUM' and tabname = 'ACCOUNT_XREF' and tabschema in ('DNTNCK');
Distributed Queries Using Materialized Query Tables (MQTs) on Nicknames
If your distributed query matches one of the following:
you can speed up the distributed query by using MQTs on nickname:
The following should be noted:
Analyzing Query Optimization
There are two utilities provided with DB2 that show global access plans:
For additional information, refer to Appendix F: Explain Tools.