联系：手机/微信(+86 17813235971) QQ(107644445)
标题：DB2中schema管理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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内部没有用户的概念,连接用户必须是操作系统用户.