与oracle数据库不同，DB2中的schema并不直接和一个数据库用户绑定，虽然他们会有默认的绑定关系，但是DB2可以独立创建一个不存在用户关联的schema来进行使用，现在验证调查如下：
1、查看当前的schema
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
2、查看当前的数据库用户
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> id
uid=3014(db2inst1) gid=899(db2iadm1) groups=16(dialout),33(video),899(db2iadm1)
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
3、查看当前存在的schema
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
ERRORSCHEMA
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
13 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
4、创建一个独立的schema
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "create schema test"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
5、再次查看schema清单：
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
ERRORSCHEMA
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
TEST
14 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
6、在当前的schema中创建表
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "create table test (id int)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2014-01-07-15.58.52.866509
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
7、切换schema，再创建表
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "set current schema='TEST'"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
TEST
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 "create table test2 (id int)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 list tables for schema test
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST TEST T 2014-01-07-16.53.01.576652
TEST2 TEST T 2014-01-07-16.56.00.439685
2 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001>
可以看见，创建的表已经在手动设置的schema test下面了；
但是，为什么直接list tables显示的是db2inst1的sechema下面的表？：
db2inst1@baseDB2:/db2home/db2inst1/database/db2inst1/NODE0000/SQL00001> db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2014-01-07-15.58.52.866509
原因是，即使使用了 "set current schema='TEST'"，也不能改变用户db2inst1的默认schema是db2inst1这个事实，而db2 list tables，查看的是默认schema中的表。
8、删除schema
删除schema需要使用函数
CALL SYSPROC.ADMIN_DROP_SCHEMA('SCHNAME', NULL, 'ERRORSCHEMA', 'ERRORTABLE')
演示如下：
db2inst1@baseDB2:/db2home/db2inst1> db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('TEST', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : -
Parameter Name : ERRORTAB
Parameter Value : -
Return Status = 0
db2inst1@baseDB2:/db2home/db2inst1>
db2inst1@baseDB2:/db2home/db2inst1> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1
ERRORSCHEMA
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
14 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1>
schema已经被删掉了；
查询表数据：
db2inst1@baseDB2:/db2home/db2inst1> db2 "select * from test.test2"
SQL0204N "TEST.TEST2" is an undefined name. SQLSTATE=42704
db2inst1@baseDB2:/db2home/db2inst1>
但是，查看当前的schema：
db2inst1@baseDB2:/db2home/db2inst1> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
TEST
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1>
创建一张表：
db2inst1@baseDB2:/db2home/db2inst1> db2 "create table test2 (id int)"
DB20000I The SQL command completed successfully.
再次查询所有的schema：
db2inst1@baseDB2:/db2home/db2inst1> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1
ERRORSCHEMA
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
TEST
15 record(s) selected.
TEST这个schema又被重新创建了；
9、再次尝试删除schema，这次，先切换schema
db2inst1@baseDB2:/db2home/db2inst1> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
TEST
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1> db2 "set current schema='db2inst1'"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
db2inst1
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1> db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('TEST', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : -
Parameter Name : ERRORTAB
Parameter Value : -
Return Status = 0
db2inst1@baseDB2:/db2home/db2inst1> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1
ERRORSCHEMA
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
14 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1>
删除完毕，查询表：
db2inst1@baseDB2:/db2home/db2inst1> db2 "select * from test.test2"
SQL0204N "TEST.TEST2" is an undefined name. SQLSTATE=42704
db2inst1@baseDB2:/db2home/db2inst1>
不存在；
创建一个schema itest（这个schema原来不存在）下面的表：
db2inst1@baseDB2:/db2home/db2inst1> db2 "create table itest.test2 (id int)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/db2inst1> db2 "values current schema"
1
--------------------------------------------------------------------------------------------------------------------------------
db2inst1
1 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1> db2 "select SCHEMANAME from syscat.SCHEMATA"
SCHEMANAME
--------------------------------------------------------------------------------------------------------------------------------
DB2INST1
ERRORSCHEMA
ITEST
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
15 record(s) selected.
db2inst1@baseDB2:/db2home/db2inst1>
可以看到，能够自动创建一个叫itest的schema。
结论：DB2的schema和用户没有绑定关系，可以手动创建，也可以创建表的时候默认创建。
阅读(12398) | 评论(0) | 转发(0) |
