CREATE ALIAS

The CREATE ALIAS statement creates an alias on a table, view, or member of a database file.

Invocation

This statement can be embedded 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 following system authorities:
  • *USE to the Create DDM File (CRTDDMF) command
  • *EXECUTE, *READ and *ADD to the library into which the alias is created
• Administrative 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
• Administrative authority

Syntax

                  (1)
>>-CREATE---ALIAS-------alias-name------------------------------>
 
>-----FOR--+-table-name-+---+--------------------+-------------><
           '-view-name--'   '-(--member-name--)--'
 

Notes:

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

Description

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 SQL names were specified, the alias will be created in the collection or library specified by the implicit or explicit qualifier. The qualifier is the owner of the alias if a user profile with that name exists. Otherwise, the owner of the alias is the user profile or group user profile of the job invoking the statement.

If system names were specified, the alias will be created in the collection or library that is specified by the qualifier. If not qualified, the alias will be created in the same collection or library 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, the alias will be created in the current library (*CURLIB). The owner of the alias is the user profile or group user profile of the job invoking the statement.

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.

If the alias name is not a valid system name, DB2 UDB for AS/400 will generate a system name. For information on 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 server for which the alias is to be created. An alias name cannot be specified (an alias cannot refer to another alias).

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.

member-name

Identifies a member of a database file.

If a member is specified, you can only use the alias in data manipulation (DML) SQL statements. If a member name is not specified, *FIRST is used.

Notes

An alias is created as a special form of a DDM file. 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 collection or library.

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

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

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 member of the SALES table. The sales table has 12 members (one for each month of the year).

CREATE ALIAS SALES_JANUARY
  FOR SALES(JANUARY)