The CREATE SCHEMA statement defines a schema at the current server and optionally creates tables, views, aliases, indexes, and distinct types. Comments and labels may be added in the catalog description of tables, views, aliases, indexes, columns, and distinct types. Table, view, and distinct type privileges can be granted to users.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
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:
>>-CREATE SCHEMA------------------------------------------------> >--+-schema-name--+-------------------------------------+-+-----> | | .-SCHEMA-. | | | '-FOR -+--------+--system-schema-name-' | '-AUTHORIZATION--authorization-name--------------------' >--+----------------------+-------------------------------------> '-IN ASP--+-integer--+-' '-ASP-name-' >--+----------------------------------------------------+------>< | .------------------------------------------------. | | V | | '---+-COMMENT statement--------------------------+-+-' +-CREATE ALIAS statement---------------------+ +-CREATE INDEX statement---------------------+ +-CREATE SEQUENCE statement------------------+ +-CREATE TABLE statement---------------------+ +-CREATE TYPE (Array) statement--------------+ +-CREATE TYPE (Distinct) statement-----------+ +-CREATE VIEW statement----------------------+ +-GRANT (Sequence Privileges) statement------+ +-GRANT (Table or View Privileges) statement-+ +-GRANT (Type Privileges) statement----------+ | (1) | '-LABEL statement----------------------------'
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.

Adds or replaces comments in the catalog descriptions
of tables, views, indexes, aliases, types, sequences, columns, or
constraints. Comments on packages, functions, procedures, parameters,
triggers, variables, and XSR objects are not allowed. See the COMMENT
statement COMMENT.
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).
Adds or replaces labels in the catalog descriptions
of tables, views, indexes, aliases, types, sequences, columns, or
constraints in the schema. Labels on packages, functions, procedures,
triggers, variables, and XSR objects are not allowed. See the LABEL
statementLABEL.
Schema attributes: A schema is created as:
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:
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.

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:
For example:
The system-schema-name for LONGSCHEMANAME would be LONGS00001
For example:
The system name for "longschemaname" would be "long0001" The system name for "LONGSchemaName" would be LONG_00001
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#')