DB2 10.5 for Linux, UNIX, and Windows

CREATE SCHEMA statement

The CREATE SCHEMA statement defines a schema. It is also possible to create some objects and grant privileges on objects within the statement.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

An authorization ID that holds DBADM authority can create a schema with any valid schema-name or authorization-name.

An authorization ID that does not hold DBADM authority can only create a schema with a schema-name or authorization-name that matches the authorization ID of the statement.

If the statement includes a schema-SQL-statement, the privileges held by the authorization-name (which, if not specified, defaults to the authorization ID of the statement) must include at least one of the following authorities:
  • The privileges required to perform each schema-SQL-statement
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE SCHEMA------------------------------------------------>

>--+-schema-name------------------------------------+----------->
   +-AUTHORIZATION--authorization-name--------------+   
   '-schema-name--AUTHORIZATION--authorization-name-'   

>--+---------------------------+--+--------------------------+-><
   '-DATA CAPTURE--+-NONE----+-'  | .----------------------. |   
                   '-CHANGES-'    | V                      | |   
                                  '---schema-SQL-statement-+-'   

Description

schema-name
An identifier that names the schema. The name must not identify a schema already described in the catalog (SQLSTATE 42710). The name cannot begin with 'SYS' (SQLSTATE 42939). The owner of the schema is the authorization ID that issued the statement.
AUTHORIZATION authorization-name
Identifies the user who is the owner of the schema. The value of authorization-name is also used to name the schema. The authorization-name must not identify a schema already described in the catalog (SQLSTATE 42710).
schema-name AUTHORIZATION authorization-name
Identifies a schema called schema-name, whose owner is authorization-name. The schema-name must not identify a schema already described in the catalog (SQLSTATE 42710). The schema-name cannot begin with 'SYS' (SQLSTATE 42939).
DATA CAPTURE
Indicates whether extra information for data replication is to be written to the log. The default is determined based on the value of database configuration parameter dft_schemas_dcc. If the value is "Yes" the default is CHANGES, otherwise the default is NONE.
NONE
Indicates that no extra information for data replication will be logged.
CHANGES
Indicates that extra information regarding SQL changes to this schema will be written to the log. This option is required if this schema will be replicated and a replication capture program is used to capture changes for this schema from the log.
schema-SQL-statement
SQL statements that can be included as part of the CREATE SCHEMA statement are:
  • CREATE TABLE statement, excluding typed tables and materialized query tables
  • CREATE VIEW statement, excluding typed views
  • CREATE INDEX statement
  • COMMENT statement
  • GRANT statement

Notes

Examples