oracle和db2的schema,DB2中schema管理 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648...

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内部没有用户的概念,连接用户必须是操作系统用户.