Nickname statistics update facility - overview

You retrieve the statistics for a nickname to provide the query optimizer with accurate and current information about the nickname. The optimizer uses this information to determine an optimal access plan for a query.

When you register a nickname for a data source object, the federated server adds information about that data source object to the system catalog on the federated database. The query optimizer uses this information to plan how to retrieve data from the data source object. The federated database does not automatically detect changes to the data source objects, so the information in the system catalog can become outdated.

You can retrieve the currently available statistics on database nicknames, columns, and indexes from the remote data source. Typically, you might use the Db2® command line processor to manually update nickname statistics when new statistics are collected on a remote table.

You can retrieve nickname statistics for the following relational data sources:
  • Db2 family (DRDA)
  • Informix®
  • JDBC
  • Microsoft SQL Server
  • ODBC
  • Oracle (NET8)
  • Sybase (CTLIB)
  • Teradata
You can retrieve nickname statistics for the following nonrelational data sources:
  • BioRS
  • Excel
  • Table-structured files
  • XML on the root nickname

The statistics collected for each data source varies.

You can update the following statistics using the catalog-based method for statistics collection:
  • CARD
  • FPAGES
  • NPAGES
  • OVERFLOW
  • COLCARD
  • HIGH2KEY
  • LOW2KEY
  • NLEAF
  • NLEVELS
  • CLUSTERFACTOR
  • CLUSTERRATIO
  • FULLKEYCARD
  • FIRSTKEYCARD
You can update the following statistics using the data-based method for statistics collection:
  • CARD
  • COLCARD
  • HIGH2KEY
  • LOW2KEY
  • FULLKEYCARD
  • FIRSTKEYCARD

You can retrieve the statistics of a single nickname or all nicknames in a schema on a specific server definition. You can also retrieve statistics for nicknames on data source objects with Oracle Label Security. If the database is restricted, only users with the appropriate access can view the HIGH2KEY and LOW2KEY statistics. For unrestricted databases, the nickname statistics on objects with Oracle Label Security are exposed and might pose a security risk. In those instances, you can restrict access to the system catalogs that contain sensitive information. If any part of the retrieval fails, the database rolls back the changes.

You can retrieve nickname statistics on any operating system that supports the federated server.