DB2中schema管理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

此条目发表在 DB2 分类目录。将固定链接加入收藏夹。

评论功能已关闭。