DB2 - Alias

Db2 - Aliases

Introduction

An alias in Db2 is a public or private alternative name for a database object such as a table, view, sequence, or another alias. They are primarily used to provide location transparency and to create a stable, simplified interface for applications, insulating them from changes in the underlying database schema.

It’s important to distinguish an alias from a view:

• An Alias is simply a pointer or a nickname. It provides no logical abstraction.
• A View is a stored query that can join tables, select specific columns, and contain business logic.

Creating an Alias

The syntax for creating an alias is straightforward.

Syntax:

CREATE ALIAS <alias_name> FOR <object_name>;

Practical Scenario: Imagine your application code is designed to work with a table named APP.CUSTOMERS. However, the DBA team stores the actual data in a versioned table, DATA_V1.CUSTOMER_MASTER. An alias can bridge this gap.

Example: Creating an alias APP.CUSTOMERS that points to the actual table.

db2 "CREATE ALIAS APP.CUSTOMERS FOR DATA_V1.CUSTOMER_MASTER"

Now, the application can perform all its operations on APP.CUSTOMERS without needing to know the real table’s name or location.

Using an Alias

Once created, an alias can be used in any SQL statement exactly as you would use the original object name.

Example: The application queries the data using the simple alias name.

db2 "SELECT CUST_ID, NAME FROM APP.CUSTOMERS WHERE REGION = 'North'"

Db2 automatically resolves APP.CUSTOMERS to DATA_V1.CUSTOMER_MASTER and executes the query against the underlying table.

Best Practices and Use Cases

• Application Abstraction: The primary use case. If the DBA team later migrates the data to DATA_V2.CUSTOMER_MASTER_ARCHIVE, they only need to drop and recreate the alias. No application code changes are required.
• Location Transparency: Aliases can point to objects in a different federated database, hiding the complexity of remote data access from the end-user or application.
• Simplifying Names: Create shorter, more convenient names for tables with long or complex names, especially in interactive query sessions.
• Permissions: Note that permissions are always checked against the underlying object, not the alias itself. A user must have SELECT privilege on DATA_V1.CUSTOMER_MASTER to be able to select from APP.CUSTOMERS.

Dropping an Alias

To remove an alias, use the DROP ALIAS command. This does not affect the underlying object in any way.

Syntax:

DROP ALIAS <alias_name>;

Example:

db2 "DROP ALIAS APP.CUSTOMERS"