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.

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.

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.