Creating index specifications on Informix synonyms

This topic describes the action that the federated server takes for Informix synonyms based on a table or on a view:

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

In Informix, you can create a synonym for a table or view. While the federated server allows you to create nicknames for Informix synonyms, the action that the federated server takes depends on whether the synonym is based on a table or a view:
  • Suppose that a nickname is created for a synonym, and the synonym is based on an Informix table. If the federated server determines that the table the synonym refers to has an index, then an index specification is created for the synonym. If the table that the synonym refers to does not have an index, then no index specification is created for the synonym. However you can create an index specification manually, using the CREATE INDEX statement.
  • Suppose that a nickname is created for a synonym, and the synonym is based on an Informix view. The federated server can not determine which underlying table or tables the view is based on. Therefore no index specification is created for the synonym. However you can create an index specification manually using the CREATE INDEX statement.

Restrictions

There are some restrictions when creating an index 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

The following examples describe how to create an index specification on a nickname that corresponds to an Informix synonym.

Example: A nickname is created on an Informix synonym that is based on a table

When the synonym is based on an Informix table that does not contain an index, you can create an index specification for the synonym to tell the optimizer which column or columns to search on to find data quickly. The statement you create will specify the nickname for the synonym, and you will supply information about the column or columns in the table that the synonym is based on.

In this example, you create the nickname CONTRACTS for a synonym called SALES_CONTRACTS. The table that this synonym is based on is called SALES2006_TABLE and contains several indexes: REGION, AMOUNT, SALES_REP. The CREATE INDEX statement you create will reference the nickname for the synonym and contain information about the index of the underlying table for the synonym.

To create an index specification that describes the REGION index, the syntax would be: 
CREATE UNIQUE INDEX NORTHWEST_2006_REGION ON CONTRACTS (REGION) SPECIFICATION ONLY

where NORTHWEST_2006_REGION is the index name and CONTRACTS is the nickname for the synonym SALES_CONTRACTS.

Example: A nickname is created on an Informix synonym that is based on a view

You create the nickname JP_SALES2007 for a synonym based on a view called JAPAN_SALES2007. The underlying table for this view is the JAPAN_SALES table which contains several indexes: REGION, AMOUNT, SALES_REP. The CREATE INDEX statement that you create will reference the nickname for the synonym and contain information about the index of the underlying table for the view.

When creating an index specification for a synonym based on a view, make certain that the column or columns the table index is based on, is part of the view. If you want to create index specifications for all indexes on the underlying table, each index specification must be created separately.

To create an index specification that describes the REGION index, the syntax would be: 
CREATE UNIQUE INDEX JP_2007_REGION ON JP_SALES2007 (REGION) SPECIFICATION ONLY

where JP_2007_REGION is the index name and JP_SALES2007 is the nickname for the view JAPAN_SALES2007.