Creating index specifications for data source objects

When a nickname is created for a data source table, the federated server supplies the global catalog with information about any indexes that the data source table has. The optimizer uses this information to expedite the processing of distributed requests. This information is a set of metadata and is called an index specification.

Before you begin

The privileges held by the authorization ID of the statement must include at least one of the following:
  • SYSADM or DBADM authority
  • One of CONTROL privilege on the object or INDEX privilege on the object. And one of IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the index does not exist, or CREATEIN privilege on the schema, if the schema name of the index refers to an existing schema.

About this task

The federated server does not create an index specification if:
  • A nickname is created for a table that has no index.
  • A nickname is created for a data source object that does not contain indexes such as a view or Informix synonym.
  • A nickname is created for a non-relational object, for example, a table-structured file, Excel spreadsheet, or XML tagged file.
  • The remote index is on a LOB column or an XML column.
  • The remote index contains a total key length greater than 1024 bytes.
  • The maximum number of key parts is more than 16.

In these circumstances the federated server does not store index specifications for the data source objects. However, for the first two items in the previous list you can supply the necessary index information to the global catalog. You can use the CREATE INDEX statement to specify the index information.

Restrictions

There are some restrictions when creating an index specification on a nickname.
  • If the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared. Also, you cannot use the INCLUDE, CLUSTER, PCTFREE, MINPCTUSED, DISALLOW REVERSE SCANS, and ALLOW REVERSE SCANS parameters in the CREATE INDEX statement.
  • UNIQUE should be specified only if the data for the index key contains unique values for every row of the data source table. The uniqueness will not be checked.
  • The sum of the stored lengths of the specified columns must not be greater than 1024.
  • No LOB column or distinct type column based on a LOB can be used as part of an index. This restriction is enforced even if the length attribute of the column is small enough to fit within the 1024-byte limit.

Procedure

To create an index, you can embed the CREATE INDEX statement in an application program or issue the statement as a dynamic SQL statement from the command line.

When used with nicknames, the CREATE INDEX statement creates an index specification in the federated global catalog; it does not create an index on the data source table.

Use the following syntax to create an index specification: 
CREATE INDEX index_name ON nickname
(column_name) SPECIFICATION ONLY
CREATE UNIQUE INDEX index_name ON nickname  
(column_name DESC) SPECIFICATION ONLY

For an index specification, column_name is the name by which the federated server references a column of a data source table.