CREATE NICKNAME statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

For data sources that require a user mapping, the privileges held by the authorization ID at the data source must include the privilege to select data from the object that the nickname represents.

To replace an existing nickname, the authorization ID of the statement must be the owner of the existing nickname (SQLSTATE 42501).

Syntax

non-relational-data-definition

nickname-column-list

nickname-column-definition

local-data-type

built-in-type

nickname-column-options

federated-column-options

unique-constraint

referential-constraint

references-clause

check-constraint

check-condition

functional-dependency

constraint-attributes

Notes:

• ¹ The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2).
• ² The FOR BIT DATA clause can be specified in any order with the other column constraints that follow.
• ³ DISABLE QUERY OPTIMIZATION is not supported for a unique or primary key constraint.

Description

OR REPLACE

Specifies to replace the definition for the nickname if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the nickname are not affected. This option is ignored if a definition for the nickname does not exist at the current server. This option can be specified only by the owner of the object.

nickname

Specifies a nickname, the identifier used by the federated server for the data source object. The nickname, including the implicit or explicit qualifier, must not identify a table, view, nickname, or alias described in the catalog. The schema name must not begin with 'SYS' (SQLSTATE 42939).

FOR remote-object-name

Specifies an identifier. For data sources that support catalog names, this is a four-part identifier with the format data-source-name.remote-catalog-name.remote-schema-name.remote-table-name. For data sources that doesn't support catalog names but support schema names, this is a three-part identifier with the formatdata-source-name.remote-schema-name.remote-table-name. For data sources that do not support catalog names and schema names, this is a two-part identifier with the formatdata-source-name.remote-table-name.

remote-catalog-name

Names the catalog to which the schema, table or view belongs. If the remote catalog name contains any special or lowercase characters, it must be enclosed by double quotation marks.

data-source-name

Names the data source that contains the table or view for which the nickname is being created. The data-source-name is the same name that was assigned to the server-name in the CREATE SERVER statement.

remote-schema-name

Names the schema to which the table or view belongs. If the remote schema name contains any special or lowercase characters, it must be enclosed by double quotation marks.

remote-table-name

Names the specific data source object (such as a table, alias of a table, or view) for which the nickname is being created. The table cannot be a declared temporary table (SQLSTATE 42995). If the remote table name contains any special or lowercase characters, it must be enclosed by double quotation marks.

For Db2® you can also specify the alias of a table, view, or nickname. For Db2 for z/OS® or Db2 for IBM® i, you can specify the alias of a table or view.

non-relational-data-definition

Defines the data that is to be accessed through a nonrelational wrapper.

nickname-column-definition

Defines the local attributes of the column for the nickname. Some wrappers require these attributes to be specified, while other wrappers allow the attributes to be determined from the data source.

column-name

Specifies the local name for the column. The name might be different than the corresponding column of the remote-object-name.

local-data-type

Specifies the local data type for the column. Some wrappers only support a subset of the SQL data types. For descriptions of specific data types, see CREATE TABLE .

built-in-type

See "CREATE TABLE" for the description of built-in data types.

nickname-column-options

Specifies additional options related to columns of the nickname.

NOT NULL

Specifies that the column does not allow null values.

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not identify a constraint that was already specified within the same CREATE NICKNAME statement (SQLSTATE 42710).

If this clause is omitted, an 18 byte long identifier that is unique among the identifiers of existing constraints defined on the nickname is generated by the system. (The identifier consists of 'SQL' followed by a sequence of 15 numeric characters generated by a timestamp-based function.)

When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name can be used as the name of an index specification that is created to support the constraint.

PRIMARY KEY

This provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause.

See PRIMARY KEY within the description of unique-constraint.

UNIQUE

This provides a shorthand method of defining a unique key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause.

See UNIQUE within the description of unique-constraint.

references-clause

This provides a shorthand method of defining a foreign key composed of a single column. Thus, if a references-clause is specified in the definition of column C, the effect is the same as if that references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column.

See references-clause within the description of referential-constraint.

CHECK (check-condition)

This provides a shorthand method of defining a check constraint that applies to a single column. See description for CHECK (check-condition).

OPTIONS

Indicates the column options that are added when the nickname is created. Some wrappers require that certain column options be specified.

column-option-name

Specifies the name of the option.

string-constant

Specifies the setting for column-option-name as a character string constant.

unique-constraint

Defines a unique or primary key constraint.

CONSTRAINT constraint-name

Names the primary key or unique constraint.

UNIQUE (column-name,...)

Defines a unique key composed of the identified columns. The identified columns must be defined as NOT NULL. Each column-name must identify a column of the nickname and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL and XQuery limits. No LOB column, distinct type column based on a LOB, or structured type column can be used as part of a unique key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).

The set of columns in the unique key cannot be the same as the set of columns in the primary key or another unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)

The description of the nickname as recorded in the catalog includes the unique key and its index specification. An index specification will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index specification will be the same as the constraint-name if this does not conflict with an existing index or index specification in the schema where the nickname is created. If the name of the index specification conflicts, the name will be 'SQL' followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

PRIMARY KEY (column-name,...)

Defines a primary key composed of the identified columns. The clause must not be specified more than once, and the identified columns must be defined as NOT NULL. Each column-name must identify a column of the nickname, and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL and XQuery limits. No LOB column, distinct type column based on a LOB, or structured type column can be used as part of a primary key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).

The set of columns in the primary key cannot be the same as the set of columns in a unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)

Only one primary key can be defined on a nickname.

The description of the nickname as recorded in the catalog includes the primary key and its index specification. An index specification will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index specification will be the same as the constraint-name if this does not conflict with an existing index or index specification in the schema where the nickname is created. If the name of the index specification conflicts, the name will be 'SQL', followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

referential-constraint

Defines a referential constraint.

CONSTRAINT constraint-name

Names the referential constraint.

FOREIGN KEY (column-name,...)

Defines a referential constraint with the specified constraint-name.

Let N1 denote the object nickname of the statement. The foreign key of the referential constraint is composed of the identified columns. Each name in the list of column names must identify a column of N1, and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL and XQuery limits. Foreign keys can be defined on variable length columns whose length is greater than 255 bytes. No LOB column, distinct type column based on a LOB, or structured type column can be used as part of a foreign key (SQLSTATE 42962). There must be the same number of foreign key columns as there are in the parent key, and the data types of the corresponding columns must be compatible (SQLSTATE 42830). Two column descriptions are compatible if they have compatible data types (both columns are numeric, character string, graphic, datetime, or have the same distinct type).

references-clause

Specifies the parent table or the parent nickname, and the parent key for the referential constraint.

REFERENCES table-name or nickname

The table or nickname specified in a REFERENCES clause must identify a base table or a nickname that is described in the catalog, but must not identify a catalog table.

A referential constraint is a duplicate if its foreign key, parent key, and parent table or parent nickname are the same as the foreign key, parent key, and parent table or parent nickname of a previously specified referential constraint. Duplicate referential constraints are ignored, and a warning is returned (SQLSTATE 01543).

In the following discussion, let N2 denote the identified parent table or parent nickname, and let N1 denote the nickname being created (or altered). N1 and N2 may be the same nickname.

The specified foreign key must have the same number of columns as the parent key of N2, and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule.

The referential constraint specified by a FOREIGN KEY clause defines a relationship in which N2 is the parent and N1 is the dependent.

(column-name,...)

The parent key of a referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of N2. The same column must not be identified more than once.

The list of column names must match the set of columns (in any order) of the primary key or a unique constraint that exists on N2 (SQLSTATE 42890). If a column name list is not specified, N2 must have a primary key (SQLSTATE 42888). Omission of the column name list is an implicit specification of the columns of that primary key in the sequence originally specified.

constraint-attributes

Defines attributes associated with referential integrity or check constraints.

NOT ENFORCED

The constraint is not enforced by the database manager during normal operations, such as insert, update, or delete.

TRUSTED

The data can be trusted to conform to the constraint. TRUSTED must be used only if the data in the table is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint. This is the default option.

NOT TRUSTED

The data cannot be trusted to conform to the constraint. NOT TRUSTED is intended for cases where the data conforms to the constraint for most rows, but it is not independently known that all the rows or future additions will conform to the constraint. If a constraint is NOT TRUSTED and enabled for query optimization, then it will not be used to perform optimizations that depend on the data conforming completely to the constraint. NOT TRUSTED can be specified only for referential integrity constraints (SQLSTATE 42613).

ENABLE QUERY OPTIMIZATION

The constraint is assumed to be true and can be used for query optimization under appropriate circumstances.

DISABLE QUERY OPTIMIZATION

The constraint cannot be used for query optimization.

check-constraint

Defines a check constraint. A check-constraint is a search-condition that must evaluate to not false or that defines a functional dependency between columns.

CONSTRAINT constraint-name

Names the check constraint.

CHECK (check-condition)

Defines a check constraint. The check-condition must be true or unknown for every row of the nickname.

search-condition

The search-condition has the following restrictions:

• A column reference must be to a column of the nickname being created.
• The search-condition cannot contain a TYPE predicate.
• It cannot contain any of the following elements (SQLSTATE 42621):
  • Subqueries
  • Dereference operations or DEREF functions where the scoped reference argument is other than the object identifier (OID) column
  • CAST specifications with a SCOPE clause
  • Column functions
  • Functions that are not deterministic
  • Functions defined to have an external action
  • User-defined functions defined with either CONTAINS SQL or READS SQL DATA
  • Host variables
  • Parameter markers
  • Special registers and built-in functions that depend on the value of a special register
  • Global variables
  • References to generated columns other than the identity column

functional-dependency

Defines a functional dependency between columns.

The parent set of columns contains the identified columns that immediately precede the DETERMINED BY clause. The child set of columns contains the identified columns that immediately follow the DETERMINED BY clause. All of the restrictions on the search-condition apply to parent set and child set columns, and only simple column references are allowed in the set of columns (SQLSTATE 42621). The same column must not be identified more than once in the functional dependency (SQLSTATE 42709). The data type of the column must not be a LOB data type, a distinct type based on a LOB data type, or a structured type (SQLSTATE 42962). No column in the child set of columns can be a nullable column (SQLSTATE 42621).

If a check constraint is specified as part of a column-definition, a column reference can only be made to the same column. Check constraints specified as part of a nickname definition can have column references identifying columns previously defined in the CREATE NICKNAME statement. Check constraints are not checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined, resulting in possible errors at execution time.

FOR SERVER server-name

Specifies a server that was registered using the CREATE SERVER statement. This server will be used to access the data for the nickname.

OPTIONS

Specify configuration options for the nickname to be created. Which options you can specify depends on the data source of the object for which a nickname is being created. For a list of data sources and the nickname options that apply to each, see Data source options. Each option value is a character string constant that must be enclosed in single quotation marks.

Notes

• Examples of relational data source objects are: tables and views. Examples of nonrelational data source objects are: Documentum objects or registered tables, text files (.txt), and Microsoft Excel files (.xls).
• The data source object that the nickname references must already exist at the data source denoted by the first qualifier in remote-object-name.
• The list of supported data source data types varies from wrapper to wrapper. XML and REF data source data types are not supported by any of the wrappers. DECFLOAT data source data type is supported only by the Db2 wrapper for IBM Db2 Version 9.5 or later. When the CREATE NICKNAME statement specifies a remote-object-name that has columns with unsupported data types, an error is returned.

  LONG VARCHAR and LONG VARGRAPHIC data source data types are mapped to CLOB and DBCLOB data types, respectively. LONG VARCHAR FOR BIT DATA is mapped to BLOB.

• The maximum allowable length for index names is 128 bytes. If a nickname is being created for a relational table that has an index whose name exceeds this length, the entire name is not cataloged. Rather, it is truncated to 128 bytes. If the string formed by these characters is not unique within the schema to which the index belongs, an attempt is made to make it unique by replacing the last character with 0. If the result is still not unique, the last character is changed to 1. This process is repeated with numbers 2 through 9 and, if necessary, with numbers 0 through 9 for the name's 127th character, 126th character, and so on, until a unique name is generated. To illustrate: The 130-byte name of an index on a data source table is AREALLY...REALLYLONGNAME. The names AREALLY...REALLYLONGNA and AREALLY...REALLYLONGN0 already exist in the schema to which this index belongs. The new name is over 128 bytes; therefore, it is truncated to AREALLY...REALLYLONGNA. Because this name already exists in the schema, the truncated version is changed to AREALLY...REALLYLONGN0. Because this name also exists, the truncated version is changed to AREALLY...REALLYLONGN1. This name does not already exist in the schema, so it is accepted as a new name.
• When a nickname is created for a data source object, the names of the nickname columns are stored in the catalog. When the data source object is a table or a view, the nickname column names are created to be the same as the table or view column names. If a name exceeds the maximum allowable length for a database column name, the name is truncated to this length. If the truncated version is not unique among the other column names in the table or view, it is made unique by following the procedure described in the preceding paragraph.
• If the data source object has indexes defined, index specifications for each index are created when the nickname is created. Index specifications are not created at the data source for indexes that have:
  • Duplicate column names
  • More than 64 columns
  • More than 1024 bytes in the sum of the length of the index key parts
• If the definition of a remote data source object is changed (for example, a column is deleted or a data type is changed), the nickname should be dropped and recreated; otherwise, errors might occur when the nickname is used in an SQL statement.
• Caching and protected objects: When a nickname is created, if the data source object is not protected, ALLOW CACHING is in effect for the nickname. If the federated server can detect that the data source object is protected, DISALLOW CACHING is in effect for the nickname. The DISALLOW CACHING option ensures that each time the nickname is used, data for the appropriate authorization ID is returned from the data source at query execution time. This is done by restricting the nickname from being used in the definition of a materialized query table at the federated server, which might be being used to cache the nickname data. The ALTER NICKNAME statement can be used to change between ALLOW CACHING and DISALLOW CACHING.
• BINARY and VARBINARY types are not supported in a Federated system.
• If the remote data source is Hive, Spark, or Impala, and if the remote data source object contains a column with a large-value character type such as STRING or VARCHAR(65535), the remote column is mapped to local column of type VARCHAR(32672), and any data in excess of 32672 bytes is truncated.
• Syntax alternatives: The following syntax is supported for compatibility with previous versions of Db2:
  • ADD can be specified before nickname-option-name string-constant.
  • ADD can be specified before column-option-name string-constant.

Examples

1. Create a nickname for a view, DEPARTMENT, that is in a schema called HEDGES. This view is stored in a Db2 for z/OS data source called OS390A.

      CREATE NICKNAME DEPT
        FOR OS390A.HEDGES.DEPARTMENT

2. Select all records from the view for which a nickname was created in Example 1. The view must be referenced by its nickname. The remote view can be referenced using the name by which it is known at the data source only in pass-through sessions.
   The following statement is valid after nickname DEPT is created:

      SELECT * FROM DEPT

   The following statement is invalid:

      SELECT * FROM OS390A.HEDGES.DEPARTMENT

3. Create a nickname for the remote table JAPAN that is in a schema called salesdata. Because the schema name and table name on the data source are stored in lowercase, specify the remote schema name and table name with double quotation marks:

      CREATE NICKNAME JPSALES
        FOR asia."salesdata"."japan"

4. Create a nickname for the table-structured file DRUGDATA1.TXT. Include the FILE_PATH, COLUMN DELIMITER, KEY_COLUMN, and VALIDATE_DATA_FILE nickname options in the statement.

      CREATE NICKNAME DRUGDATA1
        (Dcode         INTEGER,
        DRUG           CHAR(20),
        MANUFACTURER   CHAR(20))
        FOR SERVER biochem_lab
        OPTIONS
          (FILE_PATH '/usr/pat/DRUGDATA1.TXT',
           COLUMN_DELIMITER ',',
           KEY_COLUMN 'DCODE',
           SORTED 'Y',
           VALIDATE_DATA_FILE 'Y')

5. Create the parent nickname CUSTOMERS over multiple XML files under the specified directory path /home/dbuser. Include the following options:
   • Column options:
     • XPATH column option for the VARCHAR(5) column named ID, indicating the element or attribute in the XML file(s) from which the column data is extracted
     • XPATH column option for the VARCHAR(16) column named NAME, indicating the element or attribute in the XML file(s) from which the column data is extracted
     • XPATH column option for the VARCHAR(30) column named ADDRESS, indicating the element or attribute in the XML file(s) from which the column data is extracted
     • PRIMARY_KEY column option for the VARCHAR(16) column named CID, which identifies the customers nickname as a parent nickname in a hierarchy of nicknames
   • Nickname options:
     • DIRECTORY_PATH nickname option to indicate the location of the XML files that provide the data
     • XPATH nickname option to indicate the element in the XML files where the data begins
     • STREAMING nickname option to indicate that the XML source data is separated and processed element by element. In this example, the element is a customer record.

      CREATE NICKNAME customers
        (id      VARCHAR(5)   OPTIONS(XPATH './@id'),
         name    VARCHAR(16)  OPTIONS(XPATH './/name'),
         address VARCHAR(30)  OPTIONS(XPATH './/address/@street'),
         cid     VARCHAR(16)  OPTIONS(PRIMARY_KEY 'YES'))
         FOR SERVER xml_server
         OPTIONS
           (DIRECTORY_PATH '/home/dbuser',
            XPATH '//customer',
            STREAMING 'YES')

6. A Hive table with the name STR_TAB contains a column with the name COL5. COL5 has the type STRING and a column length of 2 GB. When you create a nickname for STR_TAB, the column length of COL5 is reduced to 32672 bytes.

      CREATE NICKNAME "STRING_NCK" FOR "SERVER10"."STR_TAB"
      SQL1812W  Remote column COL5 with length 2147483647 was reduced to 32672. SQLSTATE=0169E

7. Table of Watson data (Presto) has three identifiers. To create a nickname for such table use four-part identifiers.

   CREATE NICKNAME PRESTO_NICK FOR PRESTO_SERVER."iceberg_data"."schema"."table_name"