A database is split into so-called schemas. A distinction is made between user-defined schemas and information schemas.
User-defined schema
Every user-defined schema in a database is assigned to one user, the owner of the schema. A user-defined schema contains metadata which defines the formal structure of the base tables, views, indexes, integrity constraints, privileges and routines, all of which are defined by the owner of the schema.
Every user-defined schema has a name and an owner, identified by a so-called authorization identifier for this schema (see "Specifying an SQL user's authorization identifier"). The schema is created with the SQL statement CREATE SCHEMA and can be modified by other SQL statements for schema definition and administration.
The statements CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, GRANT and CREATE INDEX can be specified as parts of the CREATE SCHEMA statement or as separate statements. One requirement for all SQL statements for schema definition and administration is that the DBH start statement ADD-SQL-DATABASE-CATALOG-LIST (see the “
Database Operation” manual) has been issued with the parameter
ACCESS=*PARAMETERS (CAT-ADMINISTRATION=*YES).
A schema can be deleted with the SQL statement DROP SCHEMA.
Information schemas
In addition to user-defined schemas, every database possesses two so-called information schemas with the names INFORMATION_SCHEMA and SYS_INFO_SCHEMA.
The INFORMATION_SCHEMA comprises tables containing part of the metadata for a database. Any user can query this information using an ESQL program or the utility monitor.
The SYS_INFO_SCHEMA contains system-specific data and can only be accessed by the universal user (see "SQL users with universal authorization"). The tables for the information schemas are described in the “ SQL Reference Manual Part 1: SQL Statements”.