Creating and using ALIAS names

When you refer to an existing table or view, or to a physical file that consists of multiple members, you can avoid using file overrides by creating an alias. To create an alias, use the CREATE ALIAS statement.

You can create an alias for:

  • A table or view
  • A member of a table

A table alias defines a name for the file, including the specific member name. You can use this alias name in an SQL statement in the same way that a table name is used. Unlike overrides, alias names are objects that exist until they are dropped.

For example, if there is a multiple member file MYLIB.MYFILE with members MBR1 and MBR2, an alias can be created for the second member so that SQL can easily refer to it. 

CREATE ALIAS MYLIB.MYMBR2_ALIAS FOR MYLIB.MYFILE (MBR2)

When alias MYLIB.MYMBR2_ALIAS is specified on the following insert statement, the values are inserted into member MBR2 in MYLIB.MYFILE:

INSERT INTO MYLIB.MYMBR2_ALIAS VALUES('ABC', 6)

Alias names can also be specified on DDL statements. Assume that MYLIB.MYALIAS is an alias for table MYLIB.MYTABLE. The following DROP statement drops table MYLIB.MYTABLE:

DROP TABLE MYLIB.MYALIAS

If you really want to drop the alias name instead, specify the ALIAS keyword on the drop statement: 

DROP ALIAS MYLIB.MYALIAS