Section 7.6. Schemas

7.6. Schemas

A schema is a database object used to logically group other database objects. Every database object name has two parts:

 schema_name.object_name 

This two-part name (also known as the fully qualified name) must be unique within the database. Here are some examples:

 db2admin.tab1 mary.idx1 sales.tblspace1 

When you create an object, it is always created within a schema, even if you do not explicitly specify the schema name. When you do not specify the schema name, DB2 uses the authorization ID (the ID used to connect to the database) as the object's schema. If you connect to a database as peter and in a query specify a table simply as tab1, DB2 will interpret this as peter.tab1.

NOTE

A schema does not need to map to a user ID. Any user with the appropriate authorization can create a schema. For example, assuming user peter has the correct authorizations, he can create the schema foo, where foo does not map to anything at all.

To create the schema user1, use the CREATE SCHEMA statement as follows:

 CREATE SCHEMA user1 

Or, if you are connected to the database as user1, when you create the first new object using this connection without explicitly typing the schema name, DB2 will automatically create the schema user1 and then the object. This assumes you have the appropriate authorization, in this case, the IMPLICIT_SCHEMA privilege. The following statement creates the schema user1, followed by the table table1.

 CREATE TABLE table1 (mycol int) 

If you are connected to the database as user1, you can also create objects under a different schema. In this case, explicitly indicate the schema name, for example:

 CREATE TABLE newuser.table1 (mycol int) 

This statement creates a table called table1 in schema newuser. If the schema doesn't already exist, it is created. Although running both of these CREATE TABLE statements results in two tables in the database called table1, they are different tables because one is in schema user1, and the other is in schema newuser.

NOTE

Creating schemas implicitly or explicitly requires the user to have the appropriate authorizations or privileges. Refer to Chapter 10, Implementing Security, for more details.

When you access a database object, you can omit the schema name. Let's say you are connected to the database as user1, and you issue the following statement:

 SELECT * FROM table1 

This statement references table user1.table1.

If the table you want to access is newuser.table1, you must explicitly include the schema name:

 SELECT * FROM newuser.table1 

You cannot alter a schema, but you can drop it (as long as no objects exist within the schema) and recreate it with the new definition. Use the DROP SCHEMA statement to drop a schema:

 DROP SCHEMA newuser RESTRICT 

You must specify the RESTRICT keyword; it is part of the DROP SCHEMA syntax and serves as a reminder that you cannot drop a schema unless it is unused.