DB2 aliases, synonyms, and schemas

What an alias is

An alias is a substitute name recorded in the catalog at the current server. You can define it for a table, a view, or a sequence. The alias can refer to an object at this server or at a remote server. After it exists, you use the alias anywhere you would use the table, view, or sequence name in SQL.

IBM’s classic example is a three-part name hidden behind a short local name:

1
2
3
4
5
6
CREATE ALIAS TESTTAB
  FOR USIBMSTODB22.IDEMP01.EMP;
 
SELECT EMPNO, LASTNAME
  FROM TESTTAB
 WHERE WORKDEPT = 'A00';

The object you alias does not have to exist when CREATE ALIAS runs. It must exist when a later statement actually uses the alias. That lets DBAs define the name before the remote table is available, or recreate the alias after a table is moved.

An alias name can be up to 128 characters, qualified by a schema (often described as an owner ID in older manuals). Anyone who already has authority on the target object can use the alias. There is no extra “USE ALIAS” privilege to collect.

CREATE ALIAS

The statement defines the alias and writes the definition to the catalog. Typical forms:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Local two-part name for a local table
CREATE ALIAS HR.EMP FOR HRPROD.EMPLOYEE;
 
-- Three-part target: location.schema.table
CREATE ALIAS HR.REGION1PROJ
  FOR SAN_JOSE.DSN8C10.PROJ;
 
-- Alias for a sequence
CREATE ALIAS HR.EMPID_SEQ
  FOR SEQUENCE HR.EMPLOYEE_SEQ;
 
-- Public sequence alias (sequences only)
CREATE ALIAS PUBLIC.ORDER_NUM
  FOR SEQUENCE BILLING.ORDER_SEQ;

• FOR table / view / alias — the target may be a two-part local name or a three-part name. If you alias another alias, that nested alias must be a three-part name whose first part is a remote location.
• FOR SEQUENCE — the target must be a user sequence, not the hidden sequence Db2 generates for an identity column or a DOCID. Schema names starting with SYS are restricted (SYSADM is an exception).
• PUBLIC — allowed only for sequence aliases; the alias lives in schema SYSPUBLIC so many programs can share one sequence name.

If you write a three-part name for the alias itself, the location part must be this server’s Db2 LOCATION NAME (installation panel DSNTIPR). You are creating the alias here, even if the target is remote.

When applications use three-part aliases for remote objects with DRDA access, bind a package copy at each location the alias might resolve to, and list those packages in the plan PKLIST. IBM recommends an asterisk for the location in PKLIST so you are not locked to one remote name.

DROP ALIAS

DROP ALIAS removes the catalog definition. It does not drop the table, view, or sequence the alias pointed at.

1
2
DROP ALIAS HR.EMP;
DROP ALIAS TESTTAB;

After DROP, statements that still name the alias fail at execute time. Plans and packages that referenced the alias need a rebind after you recreate it with a new target—that is the whole point of using an alias when a table moves: drop, create again with the new three-part name, rebind.

Table aliases versus location aliases

A table alias is a SQL catalog object. A location alias is a communications (DDF) mapping. When a requester application still says location PRODDB2 but you need that traffic to hit a different IP address or a different Db2 that happens to share a location name, you define a location alias instead of rewriting every three-part name. Location aliases are how shops migrate servers and support multiple Db2s that would otherwise collide on location name.

Do not mix the two in conversation with operators. “We need an alias for EMP” usually means CREATE ALIAS. “We need a location alias for DRDA” means CDB / DDF configuration.

Synonyms

A synonym was an older alternate name for a local table or view. CREATE SYNONYM produced an unqualified name owned by the creator. Synonyms could not stand in for remote three-part names the way aliases can, and they did not behave the same on Db2 LUW.

IBM’s guidance has been consistent for years: use aliases instead of synonyms. Synonyms remain only for compatibility with previous releases. On Db2 12 for z/OS, CREATE SYNONYM is not supported for new work at current application-compatibility levels. If you still see SYNONYM rows in the catalog, plan a conversion:

1
2
3
4
5
6
-- Old (do not write this on current Db2 12 APPLCOMPAT)
-- CREATE SYNONYM AUDITHDR FOR EDIENU33.EDIAUDITHDR;
 
-- Replacement
CREATE ALIAS EDIENU33.AUDITHDR
  FOR EDIENU33.EDIAUDITHDR;

A view is sometimes a better substitute than either alias or synonym when you also need to hide columns or add a WHERE clause. An alias is a name only; it does not project or filter.

Schemas

A schema is a collection of named objects and the first part of a two-part SQL name such as HR.EMPLOYEE. Tables, views, indexes, aliases, sequences, distinct types, functions, procedures, and triggers all live in schemas. A schema is not a database and not a table space. The database is a storage and administration container; the schema is a name qualifier.

Objects are assigned to a schema when they are created. You either write the qualifier explicitly (HR.EMPLOYEE) or let Db2 supply an implicit qualifier.

CREATE SCHEMA

CREATE SCHEMA defines the schema and can optionally create objects and grants in the same statement. AUTHORIZATION names the owner of the schema.

1
2
3
4
5
6
7
8
9
10
11
CREATE SCHEMA HR AUTHORIZATION HRADM
  CREATE TABLE EMPLOYEE (
    EMPNO    CHAR(6)     NOT NULL,
    LASTNAME VARCHAR(15) NOT NULL,
    WORKDEPT CHAR(3)
  )
  CREATE VIEW EMP_A00 AS
    SELECT EMPNO, LASTNAME
      FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'
  GRANT SELECT ON EMPLOYEE TO ROLE HR_READ;

Nested CREATE TABLE / CREATE VIEW names inside CREATE SCHEMA are unqualified on purpose: they belong to the schema you are creating. Many sites skip CREATE SCHEMA entirely and just CREATE TABLE HR.EMPLOYEE. Db2 still uses schema HR as the qualifier. Explicit CREATE SCHEMA is useful when you want ownership, an initial set of objects, and grants created as one unit.

Schema ownership

The schema owner (AUTHORIZATION, or the ID that first created objects in that schema) holds implicit privileges such as CREATEIN on the schema. Other users need CREATEIN (or a higher authority such as SYSADM / SYSCTRL / system DBADM) to create additional objects in someone else’s schema.

Ownership of a new object is not always the same as its schema qualifier. For some object types, an unqualified CREATE in dynamic SQL uses CURRENT SCHEMA as the qualifier and CURRENT SQLID as the owner. For other types, qualifier and owner stay aligned. When CURRENT SCHEMA and CURRENT SQLID differ, read the IBM table for that object type before you assume who owns the new table.

CURRENT SCHEMA and CURRENT SQLID

CURRENT SCHEMA and CURRENT SQLID affect dynamic SQL only. Embedded static CREATE and unqualified static SELECT use the package owner and the BIND QUALIFIER option.

1
2
3
4
5
6
7
SET CURRENT SCHEMA = 'HR';
SET CURRENT SQLID = 'HRADM';
 
-- Unqualified EMPLOYEE is HR.EMPLOYEE for this dynamic statement
SELECT EMPNO FROM EMPLOYEE;
 
VALUES CURRENT SCHEMA, CURRENT SQLID;

• CURRENT SCHEMA — fills in the missing first part of unqualified object names in dynamic SQL.
• CURRENT SQLID — the SQL authorization ID. Privilege checks for dynamic SQL use it. You can SET CURRENT SQLID to a secondary auth ID you are allowed to use. SYSADM can set it more broadly.

Beginners often SET CURRENT SQLID and wonder why a SELECT still looks in the wrong schema. If the two registers differ, SCHEMA wins for qualification and SQLID wins for “may I do this?” Keep them equal unless you have a deliberate split (for example, create objects qualified HR but owned by HRADM).

Qualified object names

Two-part names are the everyday form: schema.object. Use them for local tables, views, aliases, and sequences. Leave names unqualified only when the implicit qualifier is guaranteed (a tightly controlled dynamic session, or a package with a known QUALIFIER).

Three-part names

A three-part table name is location.schema.object. The first part is a Db2 location name. The local subsystem makes and breaks an implicit DRDA connection as needed. Special register settings travel with the statement to the remote server so the statement behaves the same way it would locally.

IBM’s recommendation: do not scatter three-part names through application SQL. Create an alias that resolves to the three-part name, code the alias in the program, and when the table moves you DROP/CREATE the alias and rebind. You must still bind package copies at each remote location the alias might reach.

1
2
3
4
5
6
CREATE ALIAS HR.REGION1PROJ
  FOR SAN_JOSE.DSN8C10.PROJ;
 
-- Application SQL stays stable:
SELECT PROJNO, PROJNAME
  FROM HR.REGION1PROJ;

Four-part distributed names

Column references can add a fourth identifier: location.schema.table.column. That is how you point at EMPNO on a remote EMPLOYEE table without relying on an implicit correlation name. You will see four-part names in generated SQL, distributed EXPLAIN output, and some dynamic statements. Day-to-day programs still prefer an alias plus a two-part column reference (HR.REGION1PROJ.PROJNO or a correlation name) because it is shorter and survives a location change.

Putting the pieces together

A typical shop standard looks like this:

• Schema names match the application area (HR, BILLING), not a personal TSO ID
• Tables are always two-part in source (HR.EMPLOYEE)
• Remote tables are hidden behind local aliases
• Dynamic SQL sessions SET CURRENT SCHEMA (and usually CURRENT SQLID) to the same application schema
• No new synonyms

Follow that pattern and “which EMP did I just query?” stops being a mystery when the same unqualified name means three different tables for three different CURRENT SCHEMA values.

Explain It Like I'm Five

A schema is the name of the toy box (HR). The toy inside is EMPLOYEE. If you only say “EMPLOYEE,” Db2 looks in the box you currently have open (CURRENT SCHEMA). An alias is a nickname sticker on the lid: TESTTAB might really mean “the EMP box in the other playroom down the hall” (a three-part remote name). If the other playroom moves, you change the sticker, not every instruction on the wall. Synonyms were old stickers that only worked in this playroom; new stickers are aliases. A location alias is a forwarding address for the whole playroom building, which is a different kind of sticker kept by the post office (DDF), not inside the toy box catalog.

Exercises

1. Write CREATE ALIAS for a local name HR.EMP that points at HRPROD.EMPLOYEE.
2. Write CREATE ALIAS that hides SAN_JOSE.DSN8C10.PROJ behind HR.REGION1PROJ, then explain why a package copy is needed at SAN_JOSE.
3. Explain in two sentences why CREATE SYNONYM is the wrong choice for a new program.
4. SET CURRENT SCHEMA to HR and CURRENT SQLID to HRADM. Which register qualifies SELECT FROM EMPLOYEE, and which register is checked for SELECT privilege?
5. Label each identifier in SANJOSE.HR.EMPLOYEE.EMPNO as location, schema, table, or column.
6. Describe one situation where you would use a location alias instead of a table alias.