SQL Reference

CREATE NICKNAME

The CREATE NICKNAME statement creates a nickname for a data source table or view.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• SYSADM or DBADM authority
• IMPLICIT_SCHEMA authority on the federated database, if the implicit or explicit schema name of the nickname does not exist
• CREATEIN privilege on the schema, if the schema name of the nickname exists

In addition, the user's authorization ID at the data source must hold the privilege to select from the data source catalog the metadata about the table or view for which the nickname is being created.

Syntax

>>-CREATE NICKNAME------nickname-----FOR--remote-object-name---><
 

Description

nickname

Names the federated server's identifier for the table or view that is referenced by remote-object-name. The nickname, including the implicit or explicit qualifier, must not identify a table, view, alias, or nickname described in the catalog. The schema name must not begin with SYS (SQLSTATE 42939).

remote-object-name

Names a three-part identifier with this format:

data-source-name.remote-schema-name.remote-table-name

where:

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 data source in the CREATE SERVER statement.

remote-schema-name

Names the schema to which the table or view belongs.

remote-table-name

Names either of the following identifiers:

• The name or an alias of a DB2 family table or view
• The name of an Oracle table or view
• The table-name cannot be a declared temporary table (SQLSTATE 42995)

Notes

• The table or view that the nickname references must already exist at the data source denoted by the first qualifier in remote-object-name.
• The federated server does not support those data source data types that correspond to the following DB2 data types: LONG VARCHAR, LONG VARGRAPHIC, DATALINK, large object (LOB) types, and user-defined types. When a nickname is defined for a data source table or view, only those columns in the table or view that have supported data types will be defined to, and can be queried from, the federated database. When the CREATE NICKNAME statement is run against a table or view that has columns with unsupported data types, an error is issued.
• Because data types might be incompatible between data sources, the federated server makes minor adjustments to store remote catalog data locally as needed. Refer to the Application Development Guide for details.
• The maximum allowable length of DB2 index names is 18 characters. If a nickname is being created for a table that has an index whose name exceeds this length, the entire name is not cataloged. Rather, DB2 truncates it to 18 characters. If the string formed by these characters is not unique within the schema to which the index belongs, DB2 attempts to make it unique by replacing the last character with 0. If the result is still not unique, DB2 changes the last character to 1. DB2 repeats this process with numbers 2 through 9, and if necessary, with numbers 0 through 9 for the name's seventeenth character, sixteenth character, and so on, until a unique name is generated. To illustrate: The index of a data source table is named ABCDEFGHIJKLMNOPQRSTUVWXYZ. The names ABCDEFGHIJKLMNOPQR and ABCDEFGHIJKLMNOPQ0 already exist in the schema to which this index belongs. The new name is over 18 characters; therefore, DB2 truncates it to ABCDEFGHIJKLMNOPQR. Because this name already exists in the schema, DB2 changes the truncated version to ABCDEFGHIJKLMNOPQ0. Because this latter name exists, too, DB2 changes the truncated version to ABCDEFGHIJKLMNOPQ1. This name does not already exist in the schema, so DB2 now accepts it as a new name.
• When a nickname is created for a table or view, DB2 stores the names of the table's or view's columns in the catalog. If a name exceeds the maximum allowable length for DB2 column names (30 characters), DB2 truncates the name to this length. If the truncated version is not unique among the other names of the table's or view's columns, DB2 makes it unique by following the procedure described in the preceding paragraph.

Examples

Example 1: Create a nickname for a view, DEPARTMENT, that is in a schema called HEDGES. This view is stored in a DB2 Universal Database for OS/390 data source called OS390A.

   CREATE NICKNAME DEPT FOR OS390A.HEDGES.DEPARTMENT

Example 2:  Select all records from the view for which a nickname was created in Example 1. The view must be referenced by its nickname. (It can be referenced it by its own name only in pass-through sessions.)

SELECT * FROM OS390A.HEDGES.DEPARTMENT	Invalid
SELECT * FROM DEPT	Valid after nickname DEPT is created