Db2: Working with aliases

Let’s see how to create, modify and use an alias in Db2. An alias can refer to a table, a sequence or a module. Aliases exist in several databases, but we’re focusing on Db2 syntax here.

Create an alias

In the simplest case:

CREATE ALIAS users FOR TABLE user

Sequences and modules

We can create aliases for the following objects:

  • FOR TABLE: for tables and views;
  • FOR SEQUENCE;
  • FOR MODULE.

TABLE is the default, so we can omit the word TABLE. We will do so in the rest of the article.

Schemas

The alias doesn’t have to be in the same schema as the target object.

Problem: Make user table in schema1 available for users who only have access to schema2.

CREATE ALIAS schema2.user FOR schema1.user

Using an alias

Once an alias exists, it can be used as if it were a real table, sequence or module:

SELECT * FROM some_alias

Delete an alias

To delete an alias:

DROP ALIAS some_alias

List aliases

To list all table aliases:

SELECT * FROM syscat.tables WHERE TYPE = 'A'

Returned columns:

  • TYPE: It’s ‘A’ for aliases;
  • TABSCHEMA, TABNAME: Alias schema and name;
  • BASE_TABSCHEMA, BASE_TABNAME: Target table schema and name.

Reference

Db2 documentation: