The CREATE NICKNAME statement defines a nickname for a data source object.
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).
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).
>>-CREATE--+------------+--NICKNAME--nickname-------------------> '-OR REPLACE-' >--+-FOR--remote-object-name------------+-----------------------> '-| non-relational-data-definition |-' >--+----------------------------------------------------------+->< | .-,-------------------------------------. | | V | | '-OPTIONS--(----nickname-option-name--string-constant-+--)-' non-relational-data-definition |--| nickname-column-list |--FOR SERVER--server-name------------| nickname-column-list .-,----------------------------------. V | |--(----+-| nickname-column-definition |-+-+--)-----------------| +-| unique-constraint |----------+ +-| referential-constraint |-----+ '-| check-constraint |-----------' nickname-column-definition |--column-name--| local-data-type |--| nickname-column-options |--| local-data-type |--+-| built-in-type |------+-----------------------------------| | (1) | '-distinct-type-name-----' built-in-type |--+-+-SMALLINT----+-------------------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+--------------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(1)-------------------. | +-+-+-+-CHARACTER-+--+-----------------------+----------+--+--------------------+-+-+ | | | '-CHAR------' '-(integer-+--------+-)-' | | (2) | | | | | | '-OCTETS-' | '-------FOR BIT DATA-' | | | | '-+-VARCHAR----------------+--(integer-+--------+-)-' | | | | '-+-CHARACTER-+--VARYING-' '-OCTETS-' | | | | '-CHAR------' | | | | .-(1M)------------------------. | | | '-+-CLOB------------------------+--+-----------------------------+--------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+--------+-)-' | | '-CHAR------' +-K-+ '-OCTETS-' | | +-M-+ | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+----------------------------------+ | | '-(integer-+-------------+-)-' | | | | '-CODEUNITS16-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | '-CODEUNITS16-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ '-CODEUNITS16-' | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+-------------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+--------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | '-XML-------------------------------------------------------------------------------' nickname-column-options .--------------------------------------------------------------------------------------------------. V | |----+----------------------------------------------------------------------------------------------+-+--| +-NOT NULL-------------------------------------------------------------------------------------+ +-+-----------------------------+--+-+-PRIMARY KEY-+--| constraint-attributes |--------------+-+ | '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | | | +-| references-clause |-----------------------------------+ | | '-CHECK--(--check-condition--)--| constraint-attributes |-' | '-| federated-column-options |-----------------------------------------------------------------' federated-column-options .-,-----------------------------------. V | |--OPTIONS--(----column-option-name--string-constant-+--)-------| unique-constraint |--+-----------------------------+--+-UNIQUE------+-------------> '-CONSTRAINT--constraint-name-' '-PRIMARY KEY-' .-,-----------. V | >--(----column-name-+--)--| constraint-attributes |-------------| referential-constraint |--+-----------------------------+--FOREIGN KEY-----------------> '-CONSTRAINT--constraint-name-' .-,-----------. V | >--(----column-name-+--)--| references-clause |-----------------| references-clause |--REFERENCES--+-table-name-+--+-----------------------+--------> '-nickname---' | .-,-----------. | | V | | '-(----column-name-+--)-' >--| constraint-attributes |------------------------------------| check-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' >--CHECK--(--| check-condition |--)-----------------------------> >--| constraint-attributes |------------------------------------| check-condition |--+-search-condition----------+--------------------------------| '-| functional-dependency |-' functional-dependency |--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--| | .-,-----------. | | .-,-----------. | | V | | | V | | '-(----column-name-+--)-' '-(----column-name-+--)-' constraint-attributes .-TRUSTED-----. |--●--NOT ENFORCED--+-------------+--●--------------------------> '-NOT TRUSTED-' .-ENABLE QUERY OPTIMIZATION------. >--+--------------------------------+--●------------------------| | (3) | '-DISABLE QUERY OPTIMIZATION-----'
For DB2® for Linux, UNIX, and Windows you can also specify the alias of a table, view, or nickname. For DB2 for z/OS® or DB2 for i, you can specify the alias of a table or view.
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.
See PRIMARY KEY within the description of unique-constraint.
See UNIQUE within the description of unique-constraint.
See references-clause within the description of referential-constraint.
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.
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.
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).
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.
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.
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.
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.
CREATE NICKNAME DEPT
FOR OS390A.HEDGES.DEPARTMENT
SELECT * FROM DEPT
SELECT * FROM OS390A.HEDGES.DEPARTMENT
CREATE NICKNAME JPSALES
FOR asia."salesdata"."japan"
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')
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')