Nickname Characteristics Affecting Global Optimization

The following sections contain nickname-specific factors that can affect global optimization.

Index Considerations

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:

• DB2 is unable to retrieve any index information from a data source during nickname creation

• You want an index for a nickname on a view

• You want to encourage the DB2 optimizer to use a specific nickname as the inner table of a nested loop join. The user can create an index on the joining column if none exists.

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:

• Manually update the statistics in the SYSSTAT.TABLES view. This approach requires fewer steps, but it will not work if structural information has changed.

• Run the equivalent of RUNSTATS at the data source. Then drop the current nickname. Re-create the nickname. Use this approach if structural information has changed.

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:

• The data underneath the nickname is infrequently updated

• The nickname is frequently joined with local data

• The nickname to a remote table is frequently queried (i.e., code or lookup tables)

you can speed up the distributed query by using MQTs on nickname:

• Make a local copy of the nickname using a MQT:

  create table tphan.mqt_account as
    (select a.acct_num, a.acct_office, b.acct_cd, b.acct_desc
    from dntnck.account_xref a, tphan.account b
    where a.acct_num=b.acct_num)
    data initially deferred refresh deferred maintained by user;
  
  refresh table tphan.mqt_account;
  

The following should be noted:

• DATA INITIALLY DEFERRED? data is not inserted into the table as part of the CREATE TABLE statement. A REFRESH TABLE statement specifying the table-name is used to insert data into the table.

• REFRESH DEFERRED? data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table reflects the result of the query only as a snapshot at the time the REFRESH TABLE statement is processed. System-maintained MQTs defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). User-maintained MQTs defined with this attribute do allow INSERT, UPDATE, or DELETE statements.

• MAINTAINED BY USER? indicates that the data in the MQT is maintained by the user. The user is allowed to perform UPDATE, DELETE, or INSERT operations against user-maintained MQTs. The REFRESH TABLE statement, used for system-maintained MQTs, cannot be invoked against user-maintained MQTs. Only a REFRESH DEFERRED MQT can be defined as MAINTAINED BY USER.

Analyzing Query Optimization

There are two utilities provided with DB2 that show global access plans:

• Visual explain. Start it with the db2cc command. Use it to view the query access plan graph. The execution location for each operator is included in the detailed display of an operator. You can also find the remote SQL statement generated for each data source in the RQUERY (select operation) operator. By examining the details of each operator, you can see the number of rows estimated by the DB2 optimizer as input to and output from each operator. You can also see the estimated cost to execute each operator, including the communications cost.

• SQL explain. Start it with the db2expln command. Use it to view the access plan strategy as text. SQL explain does not provide cost information; however, you can get the access plan generated by the remote optimizer for those data sources supported by the remote explain function.