Db2 SQL Identifiers and Qualified Names

What SQL identifiers are

An identifier is a user-supplied name in SQL. You use identifiers for schemas, tables, views, indexes, columns, aliases, constraints, correlation names, and many other objects. Keywords like SELECT are not identifiers—you cannot use a reserved keyword as an ordinary identifier unless you delimit it (and even then, standards often forbid the practice).

Ordinary identifiers

An ordinary identifier follows Db2’s ordinary naming rules. In practical beginner terms:

• It begins with a letter
• It continues with letters, digits, or underscores (as allowed by the rules)
• It does not contain spaces or most special characters
• Db2 folds it to uppercase for storage and comparison

So employee, Employee, and EMPLOYEE written as ordinary identifiers all refer to EMPLOYEE. Catalog displays and EXPLAIN output typically show the uppercase form. Mainframe shops often standardize on ordinary uppercase names in DDL for predictability.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMPLOYEE (
  EMP_ID    CHAR(6)     NOT NULL,
  EMP_NAME  VARCHAR(40) NOT NULL,
  DEPT_ID   CHAR(3)
);
 
SELECT emp_id, emp_name
  FROM hr.employee
 WHERE dept_id = 'A01';

In that example, ordinary identifiers are folded: the SELECT still targets HR.EMPLOYEE. Consistency in typing helps humans even when Db2 folds case.

Delimited identifiers

A delimited identifier is enclosed in double quotation marks. Delimiting lets you use names that ordinary rules reject—spaces, certain special characters, or mixed case that you want preserved. Delimited identifiers are case-sensitive as written.

1
2
3
4
5
6
7
CREATE TABLE HR."Employee Info" (
  "Emp Id"    CHAR(6)     NOT NULL,
  "Emp Name"  VARCHAR(40) NOT NULL
);
 
SELECT "Emp Id", "Emp Name"
  FROM HR."Employee Info";

When delimited names help

• Migrating names from systems that allowed spaces or mixed case
• Matching externally mandated labels that are not ordinary identifiers
• Using a reserved word as a name (usually discouraged even if possible)

When delimited names hurt

• Every reference must quote correctly or Db2 looks for a different object
• Tools, code generators, and copybooks become fussier
• Ordinary EMPLOYEE and delimited "Employee" are not the same name

Most Db2 for z/OS application standards prefer ordinary identifiers. Use delimited names when you must—not as a default style.

String literals vs identifiers — quotes matter

Single quotes mark string literals. Double quotes mark delimited identifiers. Confusing them causes confusing errors.

1
2
3
4
5
-- Predicate compares column DEPT_ID to string literal 'A01'
WHERE DEPT_ID = 'A01'
 
-- Delimited identifier reference (a name), not a character string
FROM HR."EMPLOYEE"

Qualified names

A qualified name includes one or more qualifiers that locate the object in a namespace. The most important beginner form is:

1
2
3
4
5
6
schema-name.object-name
 
Examples:
  HR.EMPLOYEE
  PAYROLL.PAY_STUB
  SYSIBM.SYSTABLES

Qualification removes ambiguity when two schemas each have an EMPLOYEE table. It also documents intent for readers of your SQL. In multi-team subsystems, unqualified names are a common source of “works in my session, fails in yours” problems.

Column qualification and correlation names

Columns can be qualified by table name or by a correlation name (alias) to resolve ambiguity in joins:

1
2
3
4
SELECT E.EMP_ID, D.DEPT_NAME
  FROM HR.EMPLOYEE E
  JOIN HR.DEPARTMENT D
    ON E.DEPT_ID = D.DEPT_ID;

Here E and D are correlation names. E.EMP_ID means “EMP_ID from the EMPLOYEE side of the join.” Qualification is not only about schemas—it also clarifies columns whenever more than one table is in scope.

Schema qualification in depth

A schema is a logical collection of named objects. The schema name is the qualifier used when objects are created and referenced. Objects are assigned to a schema at CREATE time—either because you wrote an explicit qualifier or because Db2 applied a default qualifier.

Explicit schema qualification

Writing HR.EMPLOYEE is explicit. Db2 does not guess. This is the clearest style for production SQL, reports, and training examples when the schema is known.

Unqualified names and defaults

If you write FROM EMPLOYEE with no schema, Db2 must resolve the unqualified name. Rules depend on object type and context, but for many table/view references the CURRENT SCHEMA special register participates as the default qualifier. Older and alternate paths also involve the plan or package owner’s authorization ID as a default schema in some situations. Exact resolution details are documented in IBM’s sections on qualification of unqualified object names—bookmark that topic as you advance.

1
2
3
4
5
6
7
8
-- Session default schema set to HR (example)
SET CURRENT SCHEMA = 'HR';
 
-- Resolves using CURRENT SCHEMA -> HR.EMPLOYEE (typical case)
SELECT EMP_ID FROM EMPLOYEE;
 
-- Still unambiguous regardless of CURRENT SCHEMA
SELECT EMP_ID FROM HR.EMPLOYEE;

CURRENT SCHEMA vs CURRENT SQLID

Beginners often meet two registers early: CURRENT SCHEMA influences default schema qualification for unqualified object names, while CURRENT SQLID relates to the authorization ID used for privilege checking and ownership behaviors. They can differ. Later lessons cover authorization IDs and current registers in depth; for now, remember that “who I am for security” and “which schema is my default” are related but not identical ideas.

System schemas and naming caution

Db2 reserves schemas for system use (for example schemas starting with SYS in important ways, and others described in IBM docs). Do not create application objects in system schemas. SYSIBM catalog tables are for reading metadata with proper authority—not for casual CREATE experiments.

Practical naming guidelines for Db2 beginners

• Prefer ordinary identifiers in uppercase style for new objects
• Use meaningful schema names per application or subject area (HR, FIN, CUST)
• Qualify table names in shared SQL when multiple schemas exist
• Qualify columns in multi-table queries
• Avoid delimited identifiers unless interoperability requires them
• Never confuse 'literal' with "identifier"

How identifiers show up in COBOL programs

Embedded SQL uses the same identifier rules inside EXEC SQL. Host variables are marked with a colon and follow host-language naming, not SQL identifier folding rules for the COBOL side. Object names in the SQL text still follow SQL identifier rules.

1
2
3
4
5
6
       EXEC SQL
            SELECT EMP_NAME
              INTO :WS-EMP-NAME
              FROM HR.EMPLOYEE
             WHERE EMP_ID = :WS-EMP-ID
       END-EXEC.

Explain It Like I'm Five

Names are like labels on toy boxes. A plain label EMPLOYEE is written in big capital letters so everyone reads it the same. A fancy label in quotes can say "My Cool Toys" with spaces, but then you must always say it exactly that fancy way. Putting HR. in front is like saying “the EMPLOYEE box in the HR classroom,” so you do not open the Finance classroom’s box by mistake. If you only say EMPLOYEE, the teacher looks at which classroom you are standing in (your current schema) and picks that box.

Exercises

1. Classify each as ordinary identifier, delimited identifier, or string literal: EMPLOYEE, "Employee", 'Employee'.
2. Write a SELECT that explicitly qualifies schema FIN and table INVOICE, selecting two columns.
3. Explain what can go wrong if two schemas both have TABLEX and your SQL omits the schema.
4. Given CREATE TABLE HR."Emp", write a SELECT that correctly references the table and explain why FROM HR.Emp (ordinary) would not match.
5. In a join of EMPLOYEE and DEPARTMENT, write a SELECT list that qualifies each column with a correlation name.