CREATE ALIAS

The CREATE ALIAS statement defines an alias on a table, partition of a table, view, or member of a database file at the current or remote server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
Database administrator authority

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

The following system authorities:
- *USE to the Create DDM File (CRTDDMF) command
Database administrator authority

If SQL names are specified and a user profile exists that has the same name as the library into which the alias is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

The system authority *ADD to the user profile with that name
Database administrator authority

To replace an existing alias, the privileges held by the authorization ID of the statement must include at least one of the following:

The following system authorities:
- The system authority of *OBJMGT on the alias
- All authorities needed to DROP the alias
Database administrator authority

Syntax

Description

OR REPLACE

Specifies to replace the definition for the alias 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 alias are not affected. This option is ignored if a definition for the alias does not exist at the current server.

alias-name

Names the alias. The name, including the implicit or explicit qualifier, must not be the same as an index, table, view, alias or file that already exists at the current server.

If the alias-name is qualified, the name can be a two-part or three-part name. The schema name should not be a system schema. If a three-part name is used, the first part must identify a relational database name in the relational database directory.

For more information about connecting to a remote relational database and the local directory, see SQL Programming and the Distributed Database Programming.

If SQL names were specified, the alias will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the alias will be created in the schema that is specified by the qualifier. If not qualified, the alias will be created in the same schema as the table or view for which the alias was created. If the table is not qualified and does not exist at the time the alias is created:

If the value of the CURRENT SCHEMA special register is *LIBL, the alias will be created in the current library (*CURLIB).
Otherwise, the alias will be created in the current schema.

If the alias name is not a valid system name, Db2® for i will generate a system name. For information about the rules for generating a name, see Rules for Table Name Generation.

FOR table-name or view-name

Identifies the table or view at the current or remote server for which the alias is to be defined. An alias name cannot be specified (an alias cannot refer to another alias), unless the name refers to an alias on a remote server.

If a three-part name is specified with a non-local relational database name, the alias-name must be the same as the table-name or view-name.

The table-name or view-name need not identify a table or view that exists at the time the alias is created. If the table or view does not exist when the alias is created, a warning is returned. If the table or view does not exist when the alias is used, an error is returned.

If SQL names were specified and the table-name or view-name was not qualified, then the qualifier is the implicit qualifier. For more information, see Naming conventions.

If system names were specified and the table-name or view-name is not qualified and does not exist when the alias is created, the table-name or view-name is qualified by the library in which the alias is created.

partition-name

Identifies a partition of a partitioned table.

If a partition is specified, the alias cannot be used in SQL schema statements. If a partition is not specified, all partitions in the table are included in the alias.

If a partition name is specified, a three-part name should not be specified in the FOR clause. If a three-part name is specified and identifies a different relational database than the relational database of the alias, any attempt to use the alias will fail.

member-name

Identifies a member of a database file. If a member name is not specified and the table is not a partitioned table, *FIRST is used. If a member name is not specified and the table is a partitioned table, all members (partitions) are used.

If a member is specified, the alias cannot be used in most SQL schema statements. It can be used in CREATE PROCEDURE, CREATE FUNCTION and in a CREATE TABLE with an as-result-table clause.

If a member name is specified, a three-part name should not be specified in the FOR clause. If a three-part name is specified and identifies a different relational database than the relational database of the alias, any attempt to use the alias will fail.

Notes

Alias references: An alias can be defined to reference either the system name or SQL name. Since system names are generated during create processing, it is generally recommended that the SQL name be specified.

However, if the alias specifies a reference to a three-part name and the alias will be used as a DDM file in native commands or native access, the name specified must be the system name.

The Override Database File (OVRDBF) CL command also allows the database manager to process individual members of a database file. Creating an alias over a partition of a table or member of a database file, however, is easier and performs better by eliminating the need to perform the override.

Alias attributes: An alias is created as a special form of a DDM file. Both an alias and a normal DDM file can be used in SQL, but if the alias or DDM file specifies a non-local relational database name, the specified table-name or view-name must be the same as the name of the alias.

An alias created over a distributed table is only created on the current server. For more information about distributed tables, see DB2® Multisystem.

Alias ownership: If SQL names were specified:

If a user profile with the same name as the schema into which the alias is created exists, the owner of the alias is that user profile.
Otherwise, the owner of the alias is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the alias is the user profile or group user profile of the thread executing the statement.

Alias authority: If SQL names are used, aliases are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, aliases are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the alias is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the alias.

REPLACE rules: When an alias is recreated by REPLACE:

Any existing comment or label is discarded.
Authorized users are maintained. The object owner could change.
Current journal auditing is preserved.

Packages and three-part aliases: When an application uses three-part name aliases for remote objects and DRDA access, a package for the application program must exist at each location that is specified in the three-part names. A package can be explicitly created using the CRTSQLPKG CL command. If the three-part name alias is referenced and a package does not exist, the database manager will attempt to implicitly create the package.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

The keyword SYNONYM can be used as a synonym for ALIAS.

Examples

Example 1: Create an alias named CURRENT_PROJECTS for the PROJECT table.

CREATE ALIAS CURRENT_PROJECTS
  FOR PROJECT

Example 2: Create an alias named SALES_JANUARY on the JANUARY partition of the SALES table. The sales table has 12 partitions (one for each month of the year).

CREATE ALIAS SALES_JANUARY
  FOR SALES(JANUARY)

Example 3: Create an alias named REPORTS.SALES for the SALES table in schema REPORTS on relational database USARDB.

CREATE ALIAS REPORTS.SALES
  FOR USARDB.REPORTS.SALES