Learning Visually with Examples [Electronic resources] نسخه متنی

"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

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.

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.