Db2 schemas and schema qualifiers

The objects in a relational database are organized into sets called schemas. A schema is a collection of named objects that provides a logical classification of objects in the database. The schema name of the object determines the schema that the object belongs to.

Objects are assigned to schemas when they are created. That is, when certain types of database objects are created, they are given qualified two-part names. The first part is the schema name (or the qualifier), which is either implicitly or explicitly specified. The second part is the name of the object. The default schema is the authorization ID of the owner of the plan or package. The objects that a schema can contain include tables, indexes, table spaces, distinct types, functions, stored procedures, and triggers.

User objects, such as a distinct types, functions, procedures, sequences, or triggers should not be created in a system schema, which is any one of a set of schemas that are reserved for use by the Db2 subsystem. For more information, see Reserved schema names in Db2 for z/OS.

How CURRENT SCHEMA and CURRENT SQLID affect object schemas

In earlier versions of Db2 for z/OS®, CREATE statements had certain restrictions when the value of CURRENT SCHEMA was different from CURRENT SQLID value. Although those restrictions no longer exist, you must consider how to determine the qualifier and owner when CURRENT SCHEMA and CURRENT SQLID contain different values. The rules for how the owner is determined depend on the type of object being created.

CURRENT SCHEMA and CURRENT SQLID affect only dynamic SQL statements. Static CREATE statements are not affected by either CURRENT SCHEMA or CURRENT SQLID.

The following table summarizes the effect of CURRENT SCHEMA in determining the schema qualifier and owner for these objects:

  • Aliases
  • Auxiliary tables
  • Created global temporary tables
  • Indexes
  • Tables
  • Views
Table 1. Schema qualifier and owner for objects
Specification of name for new object being created Schema qualifier of new object Owner of new object
name (no qualifier) value of CURRENT SCHEMA value of CURRENT SQLID
abc.name (single qualifier) abc abc
......abc.name (multiple qualifiers) abc abc

The following table summarizes the effect of CURRENT SCHEMA in determining the schema qualifier and owner for these objects:

  • User-defined type
  • User-defined function
  • Procedure
  • Sequence
  • Trigger
Table 2. Schema qualifier and owner for additional objects
Specification of name for new object being created Schema qualifier of new object Owner of new object
name (no qualifier) value of CURRENT SCHEMA value of CURRENT SQLID
abc.name (single qualifier) abc value of CURRENT SQLID
......abc.name (multiple qualifiers) abc value of CURRENT SQLID