Db2 schemas

What a schema qualifies

IBM describes a schema as a collection of named objects that provides a logical classification of objects in the database. When you create certain objects, Db2 gives them a two-part name: schema name (qualifier) plus object name. The default schema in many contexts relates to the authorization id of the owner of the plan or package, but shops often use dedicated schema names for applications (HR, FIN, SALES).

Objects a schema can contain include tables, indexes, table spaces (as named objects in the SQL sense), distinct types, functions, stored procedures, and triggers. User objects should not be created in reserved system schemas. Think of a schema as a namespace for SQL objects so HR.EMPLOYEE and PAYROLL.EMPLOYEE can both exist without colliding.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMPLOYEE (
    EMPNO     CHAR(6)      NOT NULL,
    LASTNAME  VARCHAR(15)  NOT NULL,
    PRIMARY KEY (EMPNO)
);
 
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE LASTNAME = 'HAAS';

Qualification answers “whose EMPLOYEE table?” without renaming every object with awkward prefixes glued into a single identifier.

Why beginners should care

Unqualified SQL that “works on my ID” may fail or hit the wrong table when run under another authid or with a different CURRENT SCHEMA. Production programs usually qualify critical objects or rely on carefully controlled bind defaults—not hope.

Implicit vs explicit schema

Explicit qualification means you write the schema: CREATE TABLE HR.EMPLOYEE … or SELECT … FROM HR.EMPLOYEE. Implicit qualification means you write only EMPLOYEE and Db2 fills in a schema based on rules for that statement type and environment.

1
2
3
4
5
-- Explicit
SELECT COUNT(*) FROM HR.EMPLOYEE;
 
-- Implicit (dynamic): uses CURRENT SCHEMA as the qualifier
SELECT COUNT(*) FROM EMPLOYEE;

For dynamic CREATE of tables, views, indexes, and similar objects, IBM documents how CURRENT SCHEMA and CURRENT SQLID interact: an unqualified name typically takes schema from CURRENT SCHEMA and ownership rules involving CURRENT SQLID; a name written as abc.name uses abc as the schema. Exact ownership rules differ slightly by object type—check the SQL reference when you automate DDL.

Static SQL note

CURRENT SCHEMA and CURRENT SQLID affect dynamic SQL. Static CREATE statements in programs are not driven by those special registers the same way; qualification is resolved in the bind/package world. That split surprises people who change CURRENT SCHEMA in a session and expect bound packages to move.

Schema vs database vs collection

Three words get mixed constantly on mainframes and in cross-platform Db2 talk. Separate them.

• Schema — who qualifies the SQL name (HR.EMPLOYEE)
• Database — which administrative/storage grouping holds the table space (IN MYDB.MYTS)
• Collection — where packages live for BIND; application packaging, not table namespaces

You might create table HR.EMPLOYEE in database APPDB table space EMPTS. The schema name HR need not equal the database name APPDB. On Db2 LUW, “database” also has product-specific meanings; on z/OS, keep the IBM hierarchy in mind: subsystem → databases → table spaces → tables, with schemas cutting across as naming.

1
2
3
4
5
-- Schema HR, database APPDB, table space EMPTS
CREATE TABLE HR.EMPLOYEE (
    EMPNO CHAR(6) NOT NULL,
    PRIMARY KEY (EMPNO)
) IN APPDB.EMPTS;

Aliases and synonyms awareness

Shops sometimes create aliases so short names resolve to schema-qualified objects. That is a convenience layer on top of schemas—not a replacement for understanding qualifiers.

CURRENT SCHEMA

CURRENT SCHEMA is a special register: a session setting that supplies the default schema qualifier for unqualified names in dynamic SQL. You can set it explicitly in environments that allow SET CURRENT SCHEMA.

1
2
3
4
SET CURRENT SCHEMA = 'HR';
 
SELECT EMPNO FROM EMPLOYEE;
-- resolves as HR.EMPLOYEE while CURRENT SCHEMA is HR

Pair this mentally with CURRENT SQLID, which influences authorization and ownership for dynamic statements. When CURRENT SCHEMA and CURRENT SQLID differ, CREATE rules decide which value becomes the schema qualifier versus the owner—object-type dependent. For day-one application SQL, the practical advice is:

• Qualify important tables in source when clarity matters
• Know your shop’s default schema for ad-hoc dynamic SQL
• Do not assume SET CURRENT SCHEMA rewires static packages

Reserved and system schemas

Db2 reserves certain schema names for the subsystem. Do not create ordinary user tables in those schemas. Follow naming standards from your DBA group—schemas are shared infrastructure, not personal scratchpads, once you leave sandbox systems.

Explain It Like I'm Five

Imagine every toy bin has a last name sticker. The sticker is the schema. “Smith.Blocks” and “Lee.Blocks” can both exist. If you just say “Blocks,” a helper assumes a default last name sticker for today—that helper is like CURRENT SCHEMA. The database is more like which closet the bins sit in for cleaning and locking. A collection is a different idea—more like which backpack holds your homework packets (programs), not the toy bins themselves.

Exercises

1. Write two CREATE TABLE statements for EMP under schemas HR and AUDIT with the same table name. Why is that legal?
2. Explain one failure mode of unqualified SELECT EMP FROM EMPLOYEE in a shared dynamic SQL tool.
3. In one sentence each, define schema, database, and collection for a teammate from a distributed Db2 background.
4. Why might SET CURRENT SCHEMA not change the table a static COBOL package reads?
5. Given IN FINDB.PAYTS and table FIN.PAYROLL, which identifier is the schema?