IBM InfoSphere Federation Server, Version 10.1

Data source statistics impact applications

When a nickname is created for a data source object, the federated database global catalog is updated with information about that object. The query optimizer uses this information to plan how to retrieve data from the object.

It is important to make sure that the data source information is current. The federated database does not automatically detect changes to data source objects.

Database object statistics stored in the global catalog

The information stored in the global catalog about a data source object, depends on the type of object. For database tables and views, the name of the object, the column names and attributes, are stored in the global catalog.

In the case of a table or nickname, the information also includes:

Statistics. For example, the number of rows and the number of pages on which the rows exist. To ensure that the federated database obtains the latest statistics, run the data source equivalent of the RUNSTATS command on the table before you create the nickname.
Index descriptions. If the table has no indexes, you can supply the catalog with metadata that an index definition typically contains. For example, assume that a nickname is created for a remote table, and that an index is subsequently created on the table at the data source. You can create an index specification at the federated server that represents this remote index. You create an index specification by issuing the CREATE INDEX statement and referencing the nickname for the table. You use the SPECIFICATION ONLY clause with the CREATE INDEX statement to produce only an index specification. The index specification informs the federated optimizer that a remote index exists. However, only metadata is generated. No index is actually created on the federated server. In addition, no statistical information is supplied to the global catalog. If you give the index specification exactly the same signature as the remote index (that is, the same name, and the same columns in the same order), you can use SYSPROC.NNSTAT to update statistics on the nickname and index specification.

To determine what data source information is stored in the global catalog, query the SYSCAT.TABLES and SYSCAT.COLUMNS catalog views. To determine what data source index information is stored in the catalog, or what a particular index specification contains, query the SYSCAT.INDEXES catalog view.

Updating statistics using the SYSSTAT view instead of the SYSCAT view

SYSCAT views are read-only catalog views in the SYSCAT schema. SYSSTAT views are updatable catalog views that contain statistical information that the optimizer uses. SYSSTAT views are in the SYSSTAT schema.

If you issue an UPDATE or INSERT operation on a view in the SYSCAT schema, it will fail. Use the updatable catalog views in the SYSSTAT schema to manually modify statistics on nicknames.

Feedback

Last updated: 2012-05-18