Creating nicknames for relational and non-relational data sources

The CREATE NICKNAME statement differs slightly for relational and nonrelational data sources.

For relational data sources the CREATE NICKNAME statement syntax is:
CREATE NICKNAME nickname_name FOR server_name."remote_schema"."object_name"
nickname_name
A unique nickname for the data source object. Nicknames can be up to 128 characters in length.
The nickname is a two-part name—the schema and the nickname. If you omit the schema when creating the nickname, the schema of the nickname will be the authentication ID of the user creating the nickname. The default values for the schema name are chosen based on the following hierarchy:
  1. CURRENT SCHEMA special register
  2. SESSION_USER special register
  3. SYSTEM USER special register
  4. Authorization ID connected to the database
FOR server_name."remote_schema"."object_name"
A three-part identifier for the remote data source object. If your data source does not support schemas, omit the schema from the CREATE NICKNAME statement.
  • server_name is the name assigned to the data source server in the CREATE SERVER statement.
  • remote_schema is the name of the remote schema to which the object belongs.
  • object_name is the name of the remote object that you want to access.
OPTIONS (options_list)
Information about the nickname that enables the SQL Query Compiler and the wrapper to efficiently execute queries.
For some nonrelational data sources the CREATE NICKNAME statement syntax is:
CREATE NICKNAME nickname_name column_definition_list
     FOR SERVER server_name
     OPTIONS (options_list)
nickname_name
A unique nickname for the data source object, as described above for relational data sources.
column_definition_list
A list of nickname columns and data types.
FOR SERVER server_name
The local name that you created for the remote server in the server definition information CREATE SERVER statement.
OPTIONS (options_list)
Information about the nickname that enables the SQL Query Compiler and the wrapper to efficiently execute queries.