db2建立schema

目录：
1.显式建立schema
2.隐式建立schema
3.查询现有的schema
4.删除schema

1.显式建立schema

执行create schema需要有DBADM权限
建立某个schema需要有SYSADM 和DBAMIN权限

总的来说就是需要SYSADM 和DBAMIN权限

--语法
CREATE SCHEMA <name> AUTHORIZATION <name>
--如果不输入AUTHORIZATION，就是执行命令的本人
db2 => create schema db2user12  
DB20000I  The SQL command completed successfully.
db2 => select SCHEMANAME,OWNER from syscat.schemata where schemaname='DB2USER12'

SCHEMANAME                     OWNER      
------------------------------ -----------
DB2USER12                      DB2INST1   

  1 record(s) selected.

2.隐式建立schema
如果你没有SYSADM,DBADMIN权限，但是你有IMPLICIT_SCHEMA，那么你也可以建立schema

--查询用户没有DBADMIN，但是有IMPLICIT_SCHEMA
db2 => GET AUTHORIZATIONS

 Administrative Authorizations for Current User

 Direct SYSADM authority                    = NO
 Direct SYSCTRL authority                   = NO
 Direct SYSMAINT authority                  = NO
 Direct DBADM authority                     = NO
 Direct CREATETAB authority                 = NO
 Direct BINDADD authority                   = NO
 Direct CONNECT authority                   = NO
 Direct CREATE_NOT_FENC authority           = NO
 Direct IMPLICIT_SCHEMA authority           = NO
 Direct LOAD authority                      = NO
 Direct QUIESCE_CONNECT authority           = NO
 Direct CREATE_EXTERNAL_ROUTINE authority   = NO
 Direct SYSMON authority                    = NO

 Indirect SYSADM authority                  = YES
 Indirect SYSCTRL authority                 = NO
 Indirect SYSMAINT authority                = NO
 Indirect DBADM authority                   = NO
 Indirect CREATETAB authority               = YES
 Indirect BINDADD authority                 = YES
 Indirect CONNECT authority                 = YES
 Indirect CREATE_NOT_FENC authority         = NO
 Indirect IMPLICIT_SCHEMA authority         = YES
 Indirect LOAD authority                    = NO
 Indirect QUIESCE_CONNECT authority         = NO
 Indirect CREATE_EXTERNAL_ROUTINE authority = NO
 Indirect SYSMON authority                  = NO

--显式创立失败
db2 => create schema db2user11
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0552N  "DB2USER1" does not have the privilege to perform operation "CREATE 
SCHEMA".  SQLSTATE=42502

--隐式建立成功
db2 => create table db2user11.t1 (aaa integer)
DB20000I  The SQL command completed successfully.

--再查询现在的schema和OWNER，可以发现owner是SYSIBM
db2 =>   select SCHEMANAME,OWNER from syscat.schemata where schemaname='DB2USER11'
SCHEMANAME      OWNER                  
--------------- -----------------------
DB2USER11       SYSIBM                 

  1 record(s) selected.

3.查询现有的schema

db2 => select schemaname from syscat.schemata

SCHEMANAME                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1                                                                                                                        
DB2USER1                                                                                                                        
DB2USER11                                                                                                                       
DB2USER12                                                                                                                       
NULLID                                                                                                                          
SQLJ                                                                                                                            
SYSCAT                                                                                                                          
SYSFUN                                                                                                                          
SYSIBM                                                                                                                          
SYSIBMADM                                                                                                                       
SYSIBMINTERNAL                                                                                                                  
SYSIBMTS                                                                                                                        
SYSPROC                                                                                                                         
SYSPUBLIC                                                                                                                       
SYSSTAT                                                                                                                         
SYSTOOLS                                                                                                                        

  16 record(s) selected.

--查询有表的schema
db2 => SELECT distinct TABSCHEMA FROM SYSCAT.TABLES

TABSCHEMA                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1                                                                                                                        
DB2USER1                                                                                                                        
DB2USER11                                                                                                                       
SYSCAT                                                                                                                          
SYSIBM                                                                                                                          
SYSIBMADM                                                                                                                       
SYSPUBLIC                                                                                                                       
SYSSTAT                                                                                                                         
SYSTOOLS                                                                                                                        

  9 record(s) selected.

4.删除schema

--语法
   DROP SCHEMA <name> RESTRICT

评论

2 楼 gthao 2012-04-10  

1 楼正解。。如果schema里面有内容的话，就删除不了，会报：Error Code: -478, SQL State: 42893（不能DROP或REVOKE特定的对象，因为其他对象依赖于该对象）

1 楼 alvin198761 2012-02-28  

这个删除方式有点不行，如果你schema里面有内容的话，就删除不了