CREATE SCHEMA

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.

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)
Administrative 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
Administrative 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
Administrative authority

Syntax


>>-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----------------------------'

Notes:

Labels and comments on packages, procedures, functions, and parameters are not 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 integer: Specifies the auxiliary storage pool (ASP) in which to create the schema. The integer must be between 1 and 32. If 1 is specified, the schema is created on the system ASP. If this clause is omitted, an ASP of 1 is assumed.
IN ASP ASP-name: Specifies the auxiliary storage pool (ASP) in which to create the schema. The name must identify an auxiliary storage pool that exists at the current server.
COMMENT statement: 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 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 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.

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 packages 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 job 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.

Start of change 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. End of change

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#')