CREATE SCHEMA

Edit online

The CREATE SCHEMA statement defines a schema at the current server and optionally creates tables, views, aliases, indexes, sequences, and distinct types. Comments and labels may be added in the catalog description of these objects, and privileges can be granted to users.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • The *USE system authority to the following CL commands:
    • Create Library (CRTLIB)
    • If WITH DATA DICTIONARY is specified, Create Data Dictionary (CRTDTADCT)
  • Database administrator authority

The privileges held by the authorization ID of the statement must include at least one of the following:

  • The privileges defined for each SQL statement included in the CREATE SCHEMA statement
  • Database administrator authority

If the AUTHORIZATION clause is specified, the privileges held by the authorization ID of the statement must also include at least one of the following:

  • The system authority *ADD to the user profile identified by authorization-name
  • Database administrator authority

Syntax

Read syntax diagramSkip visual syntax diagramCREATE SCHEMAschema-nameFOR SCHEMAsystem-schema-nameAUTHORIZATIONauthorization-nameIN ASPintegerASP-nameCOMMENT statement1CREATE ALIAS statementCREATE INDEX statementCREATE SEQUENCE statementCREATE TABLE statementCREATE TYPE (Array) statementCREATE TYPE (Distinct) statementCREATE VIEW statementGRANT (Sequence Privileges) statementGRANT (Table or View Privileges) statementGRANT (Type Privileges) statementLABEL statement1
Notes:
  • 1 Only labels and comments on object types created in the schema are supported in the CREATE SCHEMA statement.

Description

schema-name
Names the schema. A schema is created using this name. If schema-name is specified, the authorization ID of the statement is the run-time authorization ID. The name must not be the same as the name of an existing schema at the current server. The name should not begin with 'SYS' or 'Q'. Such schema names indicate that the schema is a system schema.

If the schema-name is not a valid system name and a system-schema-name is not specified, SQL will generate a system name. For information about the rules for generating the name, see Rules for Schema Name Generation.

FOR SCHEMA system-schema-name
Identifies the system name of the schema. system-schema-name must not be the same as a schema that already exists at the current server. The system-schema-name must be an unqualified system identifier that is a valid system name.

If both schema-name and system-schema-name are specified, they cannot both be valid system names.

authorization-name
Identifies the authorization ID of the statement. This authorization name is also the schema-name. The name must not be the same as the name of an existing schema at the current server.
IN ASP
Specifies the auxiliary storage pool (ASP) in which to create the schema.
integer
The integer must be between 1 and 32. If 1 is specified, the schema is created on the system ASP.
ASP-name
The name must identify an auxiliary storage pool that exists at the current server.
If this clause is omitted,
  • If the name space of the invoking thread is set to an independent auxiliary storage pool (IASP), the schema will be created in the IASP name space.
  • Otherwise, an ASP of 1 is assumed.
COMMENT statement
Adds or replaces comments in the catalog descriptions of an object created in the schema. Only comments on objects created in the schema are allowed. See the COMMENT statement COMMENT.
CREATE ALIAS statement
Creates an alias into the schema. See the CREATE ALIAS statement CREATE ALIAS.
CREATE INDEX statement
Creates an index into the schema. See the CREATE INDEX statement CREATE INDEX.
CREATE SEQUENCE statement
Creates a sequence into the schema. See the CREATE SEQUENCE statement CREATE SEQUENCE.
CREATE TABLE statement
Creates a table into the schema. See the CREATE TABLE statement CREATE TABLE.
CREATE TYPE (Array) statement
Creates an array type into the schema. See the CREATE TYPE (Array) statement CREATE TYPE (array).
CREATE TYPE (Distinct) statement
Creates a user-defined distinct type into the schema. See the CREATE TYPE (Distinct) statement CREATE TYPE (distinct).
CREATE VIEW statement
Creates a view into the schema. See the CREATE VIEW statement CREATE VIEW.
GRANT (Sequence Privileges) statement
Grants privileges for sequences in the schema. See the GRANT statement GRANT (sequence privileges).
GRANT (Table or View Privileges) statement
Grants privileges for tables and views in the schema. See the GRANT statement GRANT (table or view privileges).
GRANT (Type Privileges) statement
Grants privileges for types in the schema. See the GRANT statement GRANT (type privileges).
LABEL statement
Adds or replaces labels in the catalog descriptions of an object created in the schema. Only labels on objects created in the schema are allowed. See the LABEL statement LABEL.

Notes

Schema attributes: A schema is created as:

  • A library: A library groups related objects, and allows you to find objects by name.
  • A catalog: A catalog contains descriptions of the tables, views, indexes, and other objects in the schema. A catalog consists of a set of views. For more information, see SQL Programming.
  • A journal and journal receiver: A journal QSQJRN and journal receiver QSQJRN0001 is created in the schema, and is used to record changes to all tables subsequently created in the schema. For more information, see Journal Management.

An index created over a distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, see DB2® Multisystem.

Object ownership: The owner of the schema and created objects is determined as follows:

  • If an AUTHORIZATION clause is specified, the specified authorization ID owns the schema and all objects created by the statement.
  • Otherwise, the owner of the schema and all objects created by the statement is the user profile or the group user profile of the thread executing the statement.

Object authority: If SQL names are used, the schema and any other objects are created with the system authority of *EXCLUDE on *PUBLIC and the library is created with the create authority parameter CRTAUT(*EXCLUDE). The owner is the only user having any authority to the schema. If other users require authority to the schema, the owner can grant authority to the objects created; using the CL command Grant Object Authority (GRTOBJAUT).

If system names are used, the schema and any other objects are created with the system authority given to *PUBLIC is determined by the system value QCRTAUT, and the library is created with CRTAUT(*SYSVAL). For more information about system security, see Security Reference, and SQL Programming.

If the owner of the schema is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the schema.

Object names: If a CREATE TABLE, CREATE INDEX, CREATE ALIAS, CREATE TYPE, CREATE SEQUENCE, or CREATE VIEW statement contains a qualified name for the table, index, alias, distinct type, sequence, or view being created, the schema name specified in that qualified name must be the same as the name of the schema being created. Any other object names referenced within the schema definition may be qualified by any schema name. Unqualified table, index, alias, distinct type, sequence, or view names in any SQL statement are implicitly qualified with the name of the created schema.

Delimiters are not used between the SQL statements.

SQL statement length: If the CREATE SCHEMA statement is executed via the RUNSQLSTM command, the maximum length of any individual CREATE TABLE, CREATE INDEX, CREATE TYPE, CREATE ALIAS, CREATE SEQUENCE, CREATE VIEW, COMMENT, LABEL, or GRANT statements within the CREATE SCHEMA statement is 2 097 152. Otherwise, the entire CREATE SCHEMA statement is limited to 2 097 152.

Name resolution performance: The name of the schema can affect the performance of statements that reference objects in the schema. If the length of a schema name is greater than 30, the performance of finding objects in the schema will be worse than schemas whose name length is less than or equal to 30. To minimize the performance impact, ensure that the first 5 characters of the system name and the schema name are the same.

Syntax alternatives: The COLLECTION keyword can be used as a synonym for SCHEMA for compatibility to prior releases. This keyword is non-standard and should not be used.

Deprecated features: The WITH DATA DICTIONARY clause causes an IDDU data dictionary to be created in the schema. While the clause can still be specified at the end of the CREATE SCHEMA statement and is still supported; it is not recommended.

A schema created with a data dictionary cannot contain tables with LOB, XML, or DATALINK columns. The clause has no effect on the creation of catalog views.

Rules for Schema Name Generation

A system name will be generated if a schema is created with a name that is longer than 10 characters.

The SQL name or its corresponding system name may both be used in SQL statements to access the schema once it is created. However, the SQL name is only recognized by Db2® for i and the system name must be used in other environments.

If the schema-name is an ordinary identifier and longer than 10 characters, a 10-character system-schema-name will be generated as:

  • The first 5 characters of the name
  • A 5 digit unique number

For example: 

The system-schema-name for LONGSCHEMANAME would be LONGS00001
If the schema-name is a delimited identifier and longer than 10 characters, a 10-character system-schema-name will be generated as:
  • The first 4 characters from within the delimiters will be used as the first characters of the system-schema-name.
  • If the first 4 characters are all uppercase letters, digits, or underscores, an underscore and a 5 digit unique number is appended.
  • Otherwise, a 4 digit unique number is appended.

For example: 

   The system name for "longschemaname" would be "long0001"
   The system name for "LONGSchemaName" would be LONG_00001

Examples

Example 1: Create a schema that has an inventory part table and an index over the part number. Give authority to the schema to the user profile JONES. 

   CREATE SCHEMA INVENTORY

   CREATE TABLE PART (PARTNO SMALLINT NOT NULL,
                      DESCR  VARCHAR(24),
                      QUANTITY INT)

   CREATE INDEX PARTIND ON PART (PARTNO)

   GRANT ALL ON PART TO JONES

Example 2: Create a schema using the authorization ID of SMITH. Create a student table that has a comment on the student number column. 

   CREATE SCHEMA AUTHORIZATION SMITH

   CREATE TABLE SMITH.STUDENT (STUDNBR SMALLINT NOT NULL UNIQUE,
                               LASTNAME CHAR(20),
                               FIRSTNAME CHAR(20),
                               ADDRESS CHAR(50))

   COMMENT ON STUDENT (STUDNBR IS 'THIS IS A UNIQUE ID#')