Schemas

The objects in a relational database are organized into sets called schemas. A schema provides a logical classification of objects in a relational database.

A schema name is used as the qualifier of SQL object names such as tables, views, indexes, and triggers. A schema is also called a collection or library.

A schema has a name and may have a different system name. The system name is the name used by the IBM® i operating system. Either name is acceptable wherever a schema-name is specified in SQL statements.

A schema is distinct from, and should not be confused with, an XML schema, which is a standard that describes the structure and validates the content of XML documents.

Each database manager supports a set of schemas that are reserved for use by the database manager. Such schemas are called system schemas. The schema SESSION and all schemas that start with 'SYS' and 'Q' are system schemas.

User objects must not be created in system schemas, other than SESSION. SESSION is always used as the schema name for declared temporary tables. Users should not create schemas that start with 'SYS' or 'Q'.

A schema is also an object in the relational database. It is explicitly created using the CREATE SCHEMA statement.¹ For more information, see CREATE SCHEMA.

An object that is contained in a schema is assigned to the schema when the object is created. The schema to which it is assigned is determined by the name of the object if specifically qualified with a schema name or by the default schema name if not qualified.

For example, a user creates a schema called C:

   CREATE SCHEMA C 

The user can then issue the following statement to create a table called X in schema C:

   CREATE TABLE C.X (COL1 INT)