Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET

The CREATE SCHEMA statement creates a named collection of database objects.

The CREATE SCHEMA statement has the following format:

The CREATE SCHEMA statement creates a named collection of database objects (tables, views, and privileges). Each user has a maximum of one schema per database. If an error occurs within the CREATE SCHEMA statement, the entire statement is rolled back.

The statements within the CREATE SCHEMA statement must not be separated by semicolon delimiters; however, the CREATE SCHEMA statement must be terminated by placing a semicolon after the last object definition statement (CREATE TABLE, CREATE VIEW, or GRANT).

If object definitions are omitted, an empty schema is created.

To issue grant statements in a CREATE SCHEMA statement, you must have the required privileges. Specifically, to grant a privilege on an object you do not own, you must have been granted the privilege WITH GRANT OPTION (see GRANT OPTION Clause).

If an invalid GRANT statement is issued within a CREATE SCHEMA statement, the outcome is determined as follows:

For example, if user andre has been granted SELECT WITH GRANT OPTION on table tony.mytable and issues the following GRANT statement within a CREATE SCHEMA statement:

user fred is granted SELECT privilege but not INSERT privilege, and a warning is issued.

If a CREATE SCHEMA is issued specifying an existing schema (schema_name), the DBMS Server issues an error. To add objects to an existing schema, issue the required CREATE statements outside of a CREATE SCHEMA statement.

If no schema exists for the effective user identifier, one is implicitly created when any database object is created. If a CREATE SCHEMA statement is subsequently issued for the user, the DBMS Server returns an error.

If, within a CREATE SCHEMA statement, tables are created that have referential constraints, the order of CREATE TABLE statements is not significant. This differs from the requirements for creating tables with referential constraints outside of a CREATE SCHEMA statement, where the referenced table must exist before creating a constraint that references it. For details about referential constraints, see CREATE TABLE.

Other users can reference objects in your schema if you have granted them the required permissions. To access an object in a schema other than the schema for the effective user of the session, specify the object name as follows:

For example, user harry can select data from the employees table of the accounting group (if accounting has granted harry select permission). Harry can issue the following SELECT statement:

You cannot use host language variables in an embedded CREATE SCHEMA statement.

This statement is available to all users.

The CREATE SCHEMA statement takes an exclusive lock on a page in the iischema catalog. Locks are acquired by the individual CREATE statements within the CREATE SCHEMA statement, but released only when the CREATE SCHEMA statement itself is committed. If the CREATE SCHEMA statement contains CREATE statements that acquire locks in excess of the maximum configured for the DBMS Server, the CREATE SCHEMA statement is aborted.