The SQL statements you can use with nicknames

SQL statements support the use of nicknames.

Table 1. Common SQL statements for use with nicknames
SQL statement	Description	Authorization required
ALTER NICKNAME	Modifies an existing nickname by changing the local column name, the local data type, the federated column options, or the informational constraints. The table or view at the data source is not affected.	SYSADM or DBADM ALTER or CONTROL privilege on the nickname ALTERIN privilege on the schema, if the schema name of the nickname exists Definer of the nickname, as recorded in the DEFINER column of the catalog view for the nickname
ALTER TABLE	Changes a remote table that was created through the federated database by using transparent DDL. You cannot alter tables that were created natively on the data source. Can use informational constraints to add a referential integrity constraint to a nickname.	SYSADM or DBADM ALTER or CONTROL privilege on the nickname ALTERIN privilege on the schema, if the schema name of the nickname exists
COMMENT ON	Adds or replaces comments in the catalog descriptions of various objects, including functions, function mappings, indexes, nicknames, servers, server options, type mappings, and wrappers.	SYSADM or DBADM ALTER or CONTROL privilege on the object ALTERIN privilege on the schema Definer of the object, as recorded in the DEFINER column of the catalog view for the object
CREATE ALIAS	Defines an alias for a nickname.	SYSADM or DBADM IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the alias does not exist CREATEIN privilege on the schema, if the schema name of the alias refers to an existing schema
CREATE INDEX with SPECIFICATION ONLY clause	Creates an index specification (metadata) that indicates to the query optimizer that a data source object has an index. No actual index is created, only the specification is created.	SYSADM or DBADM CONTROL or INDEX privilege on the underlying data source object — and either IMPLICIT_SCHEMA authority on the database, or CREATEIN privilege on the schema
CREATE TABLE with the OPTIONS clause	Creates a remote table through the federated database using transparent DDL.	SYSADM or DBADM CREATETAB privilege on the database and USE privilege on the table space —and either IMPLICIT_SCHEMA authority on the database, or CREATEIN privilege on the schema
CREATE TABLE with the AS fullselect and DATA INITIALLY DEFERRED REFRESH clauses	Creates a materialized query table using a fullselect that references a nickname.	SYSADM or DBADM CREATETAB privilege on the database and USE privilege on the table space —and either IMPLICIT_SCHEMA authority on the database, or CREATEIN privilege on the schema CONTROL privilege on the table or view SELECT privilege on the table or view and ALTER privilege if REFRESH DEFERRED is specified
CREATE VIEW	Creates a view that references one or more nicknames.	SYSADM or DBADM CONTROL or SELECT privilege on the nickname—and either IMPLICIT_SCHEMA authority on the database, or CREATEIN privilege on the schema
DELETE	Deletes rows from the data source object, such as a table or view that has a nickname.	SYSADM or DBADM DELETE privilege on the nickname and DELETE privilege on the underlying data source object CONTROL privilege on the underlying data source object
DROP	Deletes an object, such as a nickname, federated view, or index specification. The table, view, or index at the data source is not affected. When the tables that are created by using the transparent DDL are dropped, the corresponding nickname for that table is also dropped.	SYSADM or DBADM DROPIN privilege on the schema for the object CONTROL privilege on the object
GRANT	Grants privileges on nicknames and federated views, such as ALTER, DELETE, INDEX, INSERT, SELECT, or UPDATE. Privileges at the data source must be granted separately.	SYSADM or DBADM WITH GRANT OPTION for each identified privilege CONTROL privilege on the object
INSERT	Inserts rows into the data source object, such as a table or view that has a nickname.	SYSADM or DBADM INSERT privilege on the nickname and INSERT privilege on the underlying data source object CONTROL privilege on the underlying data source object
LOCK TABLE	Causes the remote object at the data source to be locked. Prevents concurrent application processes from changing a data source table that has a nickname.	SYSADM or DBADM SELECT privilege on the underlying table CONTROL privilege on the underlying table.
REVOKE	Revokes privileges on nicknames and federated views, such as ALTER, DELETE, INDEX, INSERT, SELECT, or UPDATE. Privileges at the data source must be revoked separately.	SYSADM or DBADM CONTROL privilege on the object
SELECT	Selects rows from the data source object, such as a table or view that has a nickname.	SYSADM or DBADM SELECT privilege on the nickname and SELECT privilege on the underlying data source object CONTROL privilege on the underlying data source object
UPDATE	Updates the values in specified columns in rows in the data source object, such as a table or view that has a nickname.	SYSADM or DBADM UPDATE privilege on the nickname and UPDATE privilege on the underlying data source object CONTROL privilege on the underlying data source object

When you submit a query to the federated database, the authorization privileges for the nickname in the query are checked. The authorization requirements of the data source object that the nickname refers to are only applied when the query is processed at the data source.

To select, insert, update, or delete data with a nickname, the authorization ID of the statement must include these privileges:

The appropriate privilege on the nickname for the federated database to accept the request
The appropriate privilege on the underlying table object for the data source to accept the request

For example to update a data source using a nickname, you need UPDATE privilege on the nickname and UPDATE privilege on the underlying data source object.