Global catalog statistics

The global catalog on the federated server contains statistical information that is used to optimize queries.

The federated server relies on statistics for data source objects for which nicknames have been defined to optimize queries that involve those nicknames. These statistics are retrieved from the data source when you create a nickname for a data source object using the CREATE NICKNAME statement. The federated database verifies the presence of the object at the data source, and then attempts to gather existing data source statistical data. Information useful to the optimizer is read from the data source catalogs and put into the global catalog on the federated server. Because some or all of the data source catalog information might be used by the optimizer, it is advisable to update statistics (using the data source command equivalent to RUNSTATS) at the data source before you create a nickname.

Catalog statistics describe the overall size of tables and views, and the range of values in associated columns. The information retrieved includes, but is not limited to:
  • The number of rows in a nickname object
  • The number of pages that a nickname occupies
  • The number of distinct values in each column of a table
  • The number of distinct values in columns of an index
  • The highest/lowest values of a column

While the federated database can retrieve the statistical data held at a data source, it cannot automatically detect updates to existing statistical data at data sources. Furthermore, the federated database has no mechanism for handling object definition or structural changes to objects at the data sources (such as when a column is added to a table).

If the statistical data or structural characteristics for a remote object on which a nickname is defined change, you have the following choices for updating the statistics:
  • Manual statistics collection
    • Run the equivalent of RUNSTATS at the data source. Then drop the current nickname and create the nickname again. This is the recommended method for updating statistics.

      An advantage of this method is that in addition to updated statistics, any information about new indexes or structural changes to the remote object will be reflected in the new nickname. A disadvantage of this method is that any views or packages based on the old nickname will be invalidated.

    • Use the stored procedure SYSPROC.NNSTAT(), available from the command line processor.

      SYSPROC.NNSTAT() only updates the nickname statistics; it does not alter the nickname to match any structural changes to the remote object. For example, if the remote object has a new column, then updating nickname statistics does not add a column to the nickname.

    • Manually update the statistics in the SYSSTAT.TABLES catalog view. Use this method only when you know that the statistical information on the remote data source is incorrect or incomplete.
  • Automatic statistics collection

    This feature runs by default to improve performance by collecting up-to-date table and nickname statistics automatically.