"http://www.w3.org/TR/l11/DTD/l11.dtd">
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.
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.