1.1 创建系统管理使用文件目录的表空间
- db2 "create tablespace newtbs01 managed by system using ('/home/db2inst3/test/newtb/newtbs01')"
1.2 创建数据库管理的使用文件的表空间
- db2 "create tablespace newtbs02 managed by database using (file '/home/db2inst3/test/newtb/newtbs02.file' 1000)"
7.9M newtbs02.file
1.3 创建数据库管理的使用多文件的表空间
- db2 "create tablespace newtbs03
- managed by database using (
- file '/home/db2inst3/test/newtb/newtbs031.file' 4000,
- file '/home/db2inst3/test/newtb/newtbs032.file' 2000)"
32M newtbs031.file
16M newtbs032.file
创建系统管理的pagesize是16k的表空间
-
[db2inst3@kvm101 newtb]$ db2 "create tablespace newtbs04 pagesize 16 k managed by system using ('/home/db2inst3/test/newtb/newtbs04')"
-
-
DB21034E The command was processed as an SQL statement because it was not a
-
valid Command Line Processor command. During SQL processing it returned:
-
SQL1582N The PAGESIZE of the table space "NEWTBS04" does not match the
-
PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table space.
- SQLSTATE=428CB
发现报错，根据提示，新建的16k的表空间和默认使用的buffer IBMDEFAULTBP不匹配
创建的数据库是8k的pagesize，而这里我们要创建16k的表空空间
在db2中表空间需要对应pagesize的buffer pool
查看下当前bufferpool情况
-
[db2inst3@kvm101 newtb]$ db2 "select char(BPNAME,20),substr(NPAGES,1,10),
-
> substr(PAGESIZE,1,10) from syscat.bufferpools"
-
-
1 2 3
-
-------------------- ---------- ----------
-
IBMDEFAULTBP -2 8192
-
- 1 record(s) selected.
新建一个16k的bufferpool
-
[db2inst3@kvm101 newtb]$ db2 "create bufferpool bp_16k size 10000 pagesize 16K"
-
DB20000I The SQL command completed successfully.
-
-
[db2inst3@kvm101 newtb]$ db2 "select char(BPNAME,20),substr(NPAGES,1,10),
-
substr(PAGESIZE,1,10) from syscat.bufferpools"
-
-
1 2 3
-
-------------------- ---------- ----------
-
IBMDEFAULTBP -2 8192
-
BP_16K 10000 16384
- 2 record(s) selected.
创建了一个16k的buffer pool,再来使用这个16k的buffer pool创建一个16k的表空间
-
[db2inst3@kvm101 newtb]$ db2 "create tablespace newtbs04 pagesize 16 k managed by system using ('/home/db2inst3/test/newtb/newtbs04') bufferpool bp_16k"
-
- DB20000I The SQL command completed successfully.
使用db2pd -d testdb -tablespace查看刚才新建的4个表空间：
The following SQL statement creates a DMS table space on an AIX system
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (DEVICE '/dev/rdblv6' 10000,
DEVICE '/dev/rdblv7' 10000,
DEVICE '/dev/rdblv8' 10000)
OVERHEAD 7.5
TRANSFERRATE 0.06
使用UNIX中的设备创建表空间（就不做实验了）
看db2信息中心说明
Example : Creating DMS table spaces on AIX?.
using three logical volumes of 10 000 pages each, and specifies their I/O characteristics:
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29023300/viewspace-2119616/ ，如需转载，请注明出处，否则将追究法律责任。
