Reference data source objects by nicknames in SQL statements

With a federated system, you use the nicknames defined for data source objects to represent the objects in your SQL statements. The federated system does not recognize fully-qualified data source, schema, and object names in SQL statements.

Data source objects must have nicknames registered in the federated database before you can include them in your queries. In general, you can specify nicknames in an SQL statement where you can specify local tables in a SQL statement.

Example: Using nicknames in SELECT, INSERT, UPDATE, and DELETE statements

You define the nickname NFXDEPT to represent a table in an Informix® table called PERSON.DEPT, where:
  • PERSON is the data source schema
  • DEPT is the data source table name

The statement SELECT * FROM NFXDEPT is allowed from the federated server. However, the statement SELECT * FROM PERSON.DEPT is not allowed (except in a pass-through session). The federated server does not have PERSON.DEPT registered as a nickname.

Example: Using nicknames in the CREATE TABLE statement

You want to create a local table based on a remote table for which you have defined a nickname. An example of the CREATE TABLE statement is:
CREATE TABLE table_name LIKE nickname