0.DB2版本信息
[db2inst1@xifenfei ~]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09050"
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and Fix Pack
"0".
Product is installed at "/opt/db2/V9.5".
1.显示syscat.schemata视图结构
[db2inst1@xifenfei ~]$ db2 "describe table syscat.schemata"
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME SYSIBM VARCHAR 128 0 No
OWNER SYSIBM VARCHAR 128 0 No
OWNERTYPE SYSIBM CHARACTER 1 0 No
DEFINER SYSIBM VARCHAR 128 0 No
DEFINERTYPE SYSIBM CHARACTER 1 0 No
CREATE_TIME SYSIBM TIMESTAMP 10 0 No
REMARKS SYSIBM VARCHAR 254 0 Yes
2.查询当前存在schema
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME OWNER CREATE_TIME
------------ ------------ ----------------------------
SYSIBM SYSIBM 2012-03-25-15.07.07.196612
SYSCAT SYSIBM 2012-03-25-15.07.07.196612
SYSFUN SYSIBM 2012-03-25-15.07.07.196612
SYSSTAT SYSIBM 2012-03-25-15.07.07.196612
SYSPROC SYSIBM 2012-03-25-15.07.07.196612
SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612
SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612
SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612
NULLID SYSIBM 2012-03-25-15.07.23.011671
SQLJ SYSIBM 2012-03-25-15.07.54.575637
SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744
11 record(s) selected.
3.显示创建schema
[db2inst1@xifenfei ~]$ db2 "create schema xifenfei"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME OWNER CREATE_TIME
------------ ------------ ----------------------------
SYSIBM SYSIBM 2012-03-25-15.07.07.196612
SYSCAT SYSIBM 2012-03-25-15.07.07.196612
SYSFUN SYSIBM 2012-03-25-15.07.07.196612
SYSSTAT SYSIBM 2012-03-25-15.07.07.196612
SYSPROC SYSIBM 2012-03-25-15.07.07.196612
SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612
SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612
SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612
NULLID SYSIBM 2012-03-25-15.07.23.011671
SQLJ SYSIBM 2012-03-25-15.07.54.575637
SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744
XIFENFEI DB2INST1 2012-04-03-12.01.12.724932
12 record(s) selected.
4.隐式创建schema
[db2inst1@xifenfei ~]$ db2 "create table xff.t_xifenfei(id int,name varchar(100))"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME OWNER CREATE_TIME
------------ ------------ ----------------------------
SYSIBM SYSIBM 2012-03-25-15.07.07.196612
SYSCAT SYSIBM 2012-03-25-15.07.07.196612
SYSFUN SYSIBM 2012-03-25-15.07.07.196612
SYSSTAT SYSIBM 2012-03-25-15.07.07.196612
SYSPROC SYSIBM 2012-03-25-15.07.07.196612
SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612
SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612
SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612
NULLID SYSIBM 2012-03-25-15.07.23.011671
SQLJ SYSIBM 2012-03-25-15.07.54.575637
SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744
XIFENFEI DB2INST1 2012-04-03-12.01.12.724932
XFF SYSIBM 2012-04-03-12.03.12.581260
13 record(s) selected.
隐式创建schema的所属用户会是SYSIBM(存放系统数据字典表SCHEMA)
5.删除schema
[db2inst1@xifenfei ~]$ db2 "drop schema xff"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "drop
schema xff". Expected tokens may include: "RESTRICT". SQLSTATE=42601
[db2inst1@xifenfei ~]$ db2 drop schema xff restrict
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"
cannot be processed because there is an object "XFF.T_XIFENFEI", of type
"TABLE", which depends on it. SQLSTATE=42893
[db2inst1@xifenfei ~]$ db2 "drop table xff.t_xifenfei"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 drop schema xff restrict
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME OWNER CREATE_TIME
------------ ------------ ----------------------------
SYSIBM SYSIBM 2012-03-25-15.07.07.196612
SYSCAT SYSIBM 2012-03-25-15.07.07.196612
SYSFUN SYSIBM 2012-03-25-15.07.07.196612
SYSSTAT SYSIBM 2012-03-25-15.07.07.196612
SYSPROC SYSIBM 2012-03-25-15.07.07.196612
SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612
SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612
SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612
NULLID SYSIBM 2012-03-25-15.07.23.011671
SQLJ SYSIBM 2012-03-25-15.07.54.575637
SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744
XIFENFEI DB2INST1 2012-04-03-12.01.12.724932
12 record(s) selected.
删除schema需要使用restrict关键字,而且该schema中无对象存在.
在DB2中的schema的概念和ORACLE中的概念有着本质的区别:在ORACLE中schema和用户是同一个;在DB2中schema不一定是用户,因为db2内部没有用户的概念,连接用户必须是操作系统用户.