While organizing your data into tables, it may also be beneficial to group tables (and other related objects) together. This is done by defining a schema through the use of the CREATE SCHEMA statement. Information about the schema is kept in the system catalog tables of the database to which you are connected. As other objects are created, they can be placed within this schema.
The syntax of the CREATE SCHEMA statement is described in detail in the SQL Reference manual. The new schema name cannot already exist in the system catalogs and it cannot begin with "SYS".
If a user has SYSADM or DBADM authority, then the user can create a schema with any valid name. When a database is created, IMPLICIT_SCHEMA authority is granted to PUBLIC (that is, to all users).
The definer of any objects created as part of the CREATE SCHEMA statement is the schema owner. This owner can GRANT and REVOKE schema privileges to other users.
This statement must be issued by a user with DBADM authority.
Schemas may also be implicitly created when a user has IMPLICIT_SCHEMA authority. With this authority, users implicitly create a schema whenever they create an object with a schema name that does not already exist.
If users do not have IMPLICIT_SCHEMA authority, the only schema they can create is one that has the same name as their own authorization ID.
To create a schema using the Control Center:
|
To create a schema using the command line, enter:
CREATE SCHEMA <name> AUTHORIZATION <name>
The following is an example of a CREATE SCHEMA statement that creates a schema for an individual user with the authorization ID "joe":
CREATE SCHEMA joeschma AUTHORIZATION joe
You may want to establish a default schema for use by unqualified object references in dynamic SQL statements issued from within a specific DB2 connection. This is done by setting the special register CURRENT SCHEMA to the schema you wish to use as the default. Any user can set this special register: no authorization is required.
The syntax of the SET SCHEMA statement is described in detail in the SQL Reference manual.
The following is an example of how to set the CURRENT SCHEMA special register:
SET CURRENT SCHEMA = 'SCHEMA01'
This statement can be used from within an application program or issued interactively. Once set, the value of the CURRENT SCHEMA special register is used as the qualifier (schema) for unqualified object references in dynamic SQL statements, with the exception of the CREATE SCHEMA statement where an unqualified reference to a database object exists.
The initial value of the CURRENT SCHEMA special register is equal to the authorization ID of the current session user.