Nicknames in DDL statements

Data source objects must have nicknames registered in the federated database before you can include them in your DDL statements. This topic provides some examples of DDL statements that you use with federated systems.

Using nicknames in the COMMENT ON statement

The COMMENT ON statement adds or replaces comments in the federated database global catalog. The COMMENT ON statement is valid with a nickname and columns that are defined on a nickname. This statement does not update data source catalogs.

Using nicknames in the GRANT and REVOKE statements

The GRANT and REVOKE statements are valid with a nickname for certain privileges and for all users and groups. However, the federated system does not issue a corresponding GRANT or REVOKE statement on the object on the data source that the nickname references.

For example, suppose that user JON creates a nickname for an Oracle table that had no index. The nickname is ORAREM1. Later, the Oracle DBA defines an index for this table. User EILEEN now wants the federated database to know that this index exists, so that the query optimizer can devise strategies to access the table more efficiently. EILEEN can inform the federated database that a new index exists, by creating an index specification for ORAREM1.

The information about the index is stored in the SYSSTAT.INDEXES catalog view. Use the GRANT statement to give EILEEN the index privilege on this nickname, so that she can create the index specification.

GRANT INDEX ON NICKNAME ORAREM1 TO USER EILEEN

To revoke user EILEEN's privileges to create an index specification on nickname ORAREM1, use the REVOKE statement:

REVOKE INDEX ON ORAREM1 FROM USER EILEEN