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

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 schema names, this is a three-part identifier with the format data-source-name.remote-schema-name.remote-table-name. For data sources that do not support schema names, this is a two-part identifier with the format data-source-name.remote-table-name.

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.

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. 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.

Examples

1. 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

2. 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"

3. 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')

4. 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')

5. 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