2.功能与操作
(1)对模式的操作
要通过命令行来创建模式，请输入以下语句：
CREATE SCHEMA <schema-name> [ AUTHORIZATION <schema-owner-name> ]其中 是模式的名称。此名称在目录中已记录的模式内必须唯一，并且不能以 SYS 开头。如果指定了可选 AUTHORIZATION 子句，那么 将成为模式所有者。如果未指定此子句，那么发出此命令的授权标识将成为模式所有者。
在删除模式之前，必须删除该模式中的所有对象或将它们移至另一个模式。
使用命令行来删除模式，输入：
DROP SCHEMA <name> RESTRICT在以下示例中，删除了模式“joeschma”：
DROP SCHEMA joeschma RESTRICT注意：模式默认和同名的用户相关联，比如创建了tpch模式，用tpch用户连接，则自动将当前模式设定为tpch,并自动有权在当前模式下创建对象，但如果用不是模式所有者的其他用户(比如db2inst1)创建了该模式下的对象，则模式所有者tpch无权访问这样的对象，除非tpch也拥有DBADM权限或者让有权限的用户显式授权，这点和Oracle的用户自动拥有模式下表权限有所不同。以下是一个简单的例子。
--切换到tpch用户-sh-3.2$ su - tpch口令：[tpch@redflag11012601 ~]$ db2 connect to sample Database Connection Information Database server = DB2/LINUXX8664 9.7.1 SQL authorization ID = TPCH Local database alias = SAMPLE--有创建表的权限[tpch@redflag11012601 ~]$ db2 "create table test1 (a varchar(10))"DB20000I The SQL command completed successfully.[tpch@redflag11012601 ~]$ db2 "insert into test1 values('abc')"DB20000I The SQL command completed successfully.[tpch@redflag11012601 ~]$ db2 "commit"DB20000I The SQL command completed successfully.[tpch@redflag11012601 ~]$ db2 -t(c) Copyright IBM Corporation 1993,2007Command Line Processor for DB2 Client 9.7.1--不指定模式db2 => select * from test1;A ----------abc 1 record(s) selected.--指定模式db2 => select * from tpch.test1; A ----------abc 1 record(s) selected.--无权访问模式下db2inst1创建的nation表db2 => select count(*) from tpch.nation; SQL0551N "TPCH" does not have the required authorization or privilege to perform operation "SELECT" on object "TPCH.NATION". SQLSTATE=42501--不改变当前操作系统用户，用db2inst1连接sample数据库db2 => connect to sample user db2inst1 using db2; Database Connection Information Database server = DB2/LINUXX8664 9.7.1 SQL authorization ID = DB2INST1 Local database alias = SAMPLE--将nation表的所有权限赋予tpch用户db2 => grant all on tpch.nation to tpch;DB20000I The SQL command completed successfully.--用tpch 连接sample数据库db2 => connect to sample user tpch using tpch;db2 => select count(*) from tpch.nation; 1 ----------- 25 1 record(s) selected.--用db2inst1用户创建另一个tpch模式下的test2表db2 => connect to sample user db2inst1 using db2;db2 => create table tpch.test2 (a varchar(10));DB20000I The SQL command completed successfully.db2 => insert into tpch.test2 values('cde');DB20000I The SQL command completed successfully.db2 => commit;DB20000I The SQL command completed successfully.db2 => connect to sample user tpch using tpch;db2 => select count(*) from tpch.test2; SQL0551N "TPCH" does not have the required authorization or privilege to perform operation "SELECT" on object "TPCH.TEST2". SQLSTATE=42501db2 => connect to sample user db2inst1 using db2;--将dbadm系统权限赋予tpch用户,但没有授予test2表的权限db2 => grant dbadm on database to tpch;DB20000I The SQL command completed successfully.db2 => connect to sample user tpch using tpch;db2 => select count(*) from tpch.test2; 1 ----------- 1 1 record(s) selected.
