DB2表空间管理详解(原创)

create tablespace语法树

>>-CREATE
--+-----------------------+---------------------------->
           +-LARGE-----------------+  
           +-REGULAR---------------+  
           | .-SYSTEM-.            |  
           '-+--------+--TEMPORARY-'  
             '-USER---'               

>--TABLESPACE--tablespace-name---------------------------------->

>--+-----------------------------------------------------------+-->
   |     .-DATABASE PARTITION GROUP-.                          |  
   '-IN--+--------------------------+--db-partition-group-name-'  

>--+--------------------------+--------------------------------->
   '-PAGESIZE--integer--+---+-'  
                       '-K-'    
   .-MANAGED BY--AUTOMATIC STORAGE--| size-attributes |---------------------.  

>--+------------------------------------------------------------------------+-->
   '-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'  
                 '-DATABASE--| database-containers |--| size-attributes |-'    

>--+---------------------------------+-------------------------->
   '-EXTENTSIZE--+-number-of-pages-+-'  
                 '-integer--+-K-+--'    
                            '-M-'       

>--+-----------------------------------+------------------------>
   '-PREFETCHSIZE--+-AUTOMATIC-------+-'  
                   +-number-of-pages-+    
                   '-integer--+-K-+--'    
                              +-M-+       
                              '-G-'       
>--+-----------------------------+------------------------------>
   '-BUFFERPOOL--bufferpool-name-'  

>--+----------------------------------+------------------------->
   '-OVERHEAD--number-of-milliseconds-'  

>--+------------------------+----------------------------------->
   +-NO FILE SYSTEM CACHING-+  
   '-FILE SYSTEM CACHING----'  

>--+--------------------------------------+--------------------->
   '-TRANSFERRATE--number-of-milliseconds-'  

>--+---------------------------------+-------------------------><
   '-DROPPED TABLE RECOVERY--+-ON--+-'  
                             '-OFF-'    
size-attributes

|--+---------------------+--+-----------------------------+----->
   '-AUTORESIZE--+-NO--+-'  '-INITIALSIZE--integer--+-K-+-'  
                 '-YES-'                            +-M-+    
                                                    '-G-'    

>--+------------------------------------+----------------------->
   '-INCREASESIZE--integer--+-PERCENT-+-'  
                            '-+-K-+---'    
                              +-M-+        
                              '-G-'        

>--+-----------------------------+------------------------------|
   '-MAXSIZE--+-integer--+-K-+-+-'  
              |          +-M-+ |    
              |          '-G-' |    
              '-NONE-----------'    

system-containers
   .----------------------------------------------------------------------.  
   |           .-,------------------.                                     |  
   V           V                    |                                     |  
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
                                          '-| on-db-partitions-clause |-'    

database-containers
   .--------------------------------------------------------------.  
   V                                                              |  
|----USING--| container-clause |--+-----------------------------+-+--|
                                  '-| on-db-partitions-clause |-'    

container-clause

      .-,---------------------------------------------------.     
      V                                                     |     
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
        '-DEVICE-'                      '-integer--+-K-+--'       
                                                   +-M-+          
                                                   '-G-'          

on-db-partitions-clause

|--ON--+-DBPARTITIONNUM--+-------------------------------------->
       '-DBPARTITIONNUMS-'  

      .-,--------------------------------------------------.     
      V                                                    |     
>--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'       

参数详解
1.LARGE, REGULAR, SYSTEM TEMPORARY, or USER TEMPORARY
指定要创建的表空间的类型，如果不指定，默认类型是由”MANAGED BY”字句决定的。
LARGE:
只存储永久性数据。这个类型只允许用在DMS表空间上。它也是创建DMS表空间的默认类型。当一个表放置在一个large类型的表空间中：
这个表可以比放置在regular类型的表空间中更大。
这个表可以支持每个data page有超过255行记录，这样可以增加空间利用效用。
这个表上的索引需要每行多出额外的2字节，和regular表空间上的索引对比。
REGULAR：
只存储永久性数据。这个类型可以用在DMS和SMS表空间。这个类型也是唯一可以用在SMS表空间的类型，同时也是SMS的默认类型。
SYSTEM TEMPORARY
存储临时表，和完成一些排序和连接等操作的工作区。一个数据库至少有一个系统临时表空间，因为临时表只能放置在这种表空间中。在数据库创建时，自动创建一个临时表空间。由于临时表空间涉及到空间回收，故常采用SMS表空间
USER TEMPORARY
存储创建的临时表和声明的临时表。当数据库被创建时，没有用户临时表空间存在。为了允许定义临时表和声明临时表，至少有一个用户临时表空间被创建，通过使用USE privileges. 由于临时表空间涉及到空间回收，故常采用SMS表空间
注:当存在多个临时表空间时，采取循环方式，平衡他们的使用。
2.tablespace-name

表空间的名称，这是一个整体名字，是一个SQL标示符。不能使用在catalog中存在的表空间名称，也不能以’SYS’开头命名表空间。表空间所有者创建表空间，针对于这个表空间，被授予了USE privilege with the WITH GRANT OPTION。
3.IN DATABASE PARTITION GROUP db-partition-group-name
为表空间指定数据库分区组。数据库分区组必须已存在。当创建系统临时表空间时，唯一允许的分区组是IBMTEMPGROUP。
‘DATABASE PARTITION GROUP’关键词是不强制的，可有可无，并不影响参数。
如果创建表空间时，没有指定分区组，Regular, Large和User Temporary类型使用IBMDEFAULTGROUP分组区；SYSTEM TEMPORARY类型，使用IBMTEMPGROUP类型。
4.PAGESIZE integer [K]
为表空间定义页大小page size。没有指定后缀K的情况下，有效值是4096,8192,16384,32768；有后缀K的情况下，有效值是4,8,16,32。如果不是这些有效值，会报错。当表空间的page size和所指定的buffer pool的page size大小不一样，也会报错。pagesize的默认值在db cfg中可以找到，这个参数是在创建数据库时设置的。
5.MANAGED BY AUTOMATIC STORAGE
指定表空间为自动存储的表空间。如果数据库没有定义automatic storage，会报错。可以为SMS或DMS指定为自动存储。
| size-attributes |语句块
AUTORESIZE：指定DMS表空间是否开启auto-resize功能，当表空间要满时，自动增加大小。其他参数见上面语法图。
6.MANAGED BY SYSTEM
指定表空间为SMS，系统管理表空间。当type没有指定，默认行为是创建regular表空间。
| system-containers |语法块，指定SMS表空间的容器。
USING (‘container-string’,…)
对于SMS表空间，指定一个或多个容器存储数据。container-string的长度超过240字节。每一个container-string可以是绝对或相对路径。
注:如果用相对路径，是相对于数据库目录，而且可以是软连接。如果指定的文件夹不存在，数据库管理器会自动创建这个文件夹。如果表空间被删除，所有被数据库管理器创建的组件也会被删除。如果指定的文件夹已经存在，这个文件夹不能包含任何文件和子目录。
container-string的格式依赖于操作系统。windows下，绝对路径以盘符和:开始；Unix中，绝对路径以/开始。相对路径形式一样。
也可以支持远程资源，如LAN-redirected drives或NFS-mounted文件系统。
| on-db-partitions-clause | 指定容器在一个分区数据库中。如果没有指定，在分区组中找一个分区创建。如果是系统临时表，则在每个分区中创建。
7.MANAGED BY DATABASE
指定表空间是DMS，数据库管理表空间。当type没有指定，默认是large。
| database-containers | 语法块，为DBS表空间指定容器。
USING |container-clause|
(FILE|DEVICE ‘container-string’ number-of-pages,…)
对于DMS表空间，指定一个或多个container存储数据。容器类型(FILE或DEVICE)和大小(没有单位，默认是page的数量)需要指定。大小也可以指定单位，K,M,G，如果指定单位，用字节数除pagesize然后下取整，来决定page个数。container-stiring不能超过254个字节。
对于FILE的容器，必须是绝对或相对路径。如果目录中没有任何组件，它会被数据库管理器创建。如果文件不存在，它会被创建和初始化到指定大小。当表空间被删除，所有组件也被删除。如果文件存在，会被覆盖，如果文件小于指定大小，会被扩展，如果大于指定大小，并不会被截断。
对于DEVICE容器，container-string必须是设备名，设备必须已存在。
所有容器必须在所有数据库中是唯一的。一个容器只能属于一个表空间。一个表空间中不同容器的大小可以不同；但是，一个表空间中的容器大小一样，可以获得更好的性能。container-string的精确的形式和操作系统有关。
远程资源也是支持的，同SMS。
|on-db-partitions-clause|，同SMS。
8.EXTENTSIZE number-of-pages
指定一个EXTENTSIZE，在跳转到下一个容器前，需要在当前容器中的写入的页数。
也可以指定单位，K,M，最后的extent size为字节数除pagesize下取整。数据库管理器会根据extent size，循环使用容器存储数据。 根据DB2对容器进行循环写入的原则，如果您选择实现磁盘条带分割以及DB2条带分割，那么表空间的扩展数据块大小和磁盘的分割大小应该相同，这样会使性能更加优化。
EXTENTSIZE的默认值在db cfg中可以找到，DFT_EXTENT_SZ，创建DB时候设置的，这个值有效值是2-256。
9.PREFETCHSIZE
在查询之前，取出查询所需要的数据，这样不需要等待I/O。
默认值由db cfg中的dft_prefetch_sz决定。
默认为AUTOMATIC指定prefetch size为自动更新，由数据库管理器控制。当容器增加等，会自动调整。
number-of-pages指定prefetch的页数，也可以用单位，也是通过字节自动计算页数。
10.BUFFERPOOL bufferpool-name
指定这个表空间使用的缓冲池，这个缓冲池必须已存在。如果没有指定，默认的缓冲池(IBMDEFAULTBP)将被使用。缓冲池的的页大小必须和表空间的页大小一样。
11.OVERHEAD number-of-milliseconds
指定I/O控制器开销和磁盘寻道和延迟时间。这个值用于在查询优化时决定I/O开销，可以是任何数字，包括浮点。如果表空的容器OVERHEAD值不同，则取平均值。db2 v9以上，这个值默认是7.5，从v9之前升级过来的，默认是12.67.
12.FILE SYSTEM CACHING or NO FILE SYSTEM CACHING
指定I/O操作是否在操作系统级别进行缓存。如果不指定这个参数，默认值如下：
在JFS on AIX, Linux System z®, all non-VxFS file systems on Solaris, HP-UX，SMS临时表空间，所有LOB和large data，都默认使用FILE SYSTEM CACHING。在所有其他平台和文件系统中，默认使用NO FILE SYSTEM
FILE SYSTEM CACHING在目标表空间上的I/O操作，会在操作系统级别上进行缓存。
NO FILE SYSTEM CACHING指定I/O操作绕过操作系统级别缓存。
13.TRANSFERRATE number-of-milliseconds
指定读一个page到内存的时间。这个值用来决定在查询优化时，I/O的成本。可以是浮点数，如果每个容器的值不同，取平均。V9以前版本，创建的DB，默认值是0.06毫秒，从V9以前版本升级到V9之后，默认值是0.18毫秒。
14.DROPPED TABLE RECOVERY
指明被删除的表是否可以被恢复，通过使用”ROLLFORWARD DATABASE … RECOVER DROPPED TABLE”。
ON，指定被删除的表，可以恢复。这是V8之后是默认值。
OFF，被删除的表不能恢复。V7的默认值。

表空间日常管理命令
本例创建的表空间pagesize大部分为32kb，故
1、创建pagesize为32kb的bufferpool
create bufferpool bp32k size 2000 pagesize 32k
2、查看bufferpool属性
select * from syscat.bufferpools
3、更改缓冲池大小
alter bufferpool bp32k size 2000

4、创建数据页为32kb数据块为1024kb预取值为默认，且不使用文件系统缓存的大型DMS表空间

create large tablespace tbs_data pagesize 32k managed by database using (file '/database/tbs_data/cont0' 100M,file '/database/tbs_data/cont1' 100M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching

5、创建 数据页为32kb的SMS系统临时表空间
create temporary tablespace tbs_temp pagesize 32k managed by system using ('/database/tbs_temp') bufferpool bp32k

6、创建 数据页为32kb的SMS用户临时表空间

create user temporary tablespace tbs_user_temp pagesize 32k managed by system using('/database/tbs_usertemp') bufferpool bp32k
7、创建 数据页为32kb的自动存储管理表空间
create tablespace tbs_index pagesize 32k bufferpool bp32k

8、创建初始大小为100MB，增量为100MB，最大大小为100GB的 自动存储管理表空间
create tablespace tbs_data2 initialsize 100M increasesize 100M maxsize 100G
9、查看表空间信息
list tablespaces
list tablespaces show detail
db2pd -d testdb -tablespaces
get snapshot for tablespaces on testdb
也可以查看sysibmadm.snaptbsp和sysibmadm.snapcontainer这两个视图
10、查看表空相关的容器信息
list tablespace containers for 7    #tablespace id
list tablespace containers for 7 show detail
11、如果DMS表空间对应的存储中还有未分配空间，可通过alter tablespace的extend、reduce或resize选项扩展已有表空间容器的大小。其中extend用来扩展容器大小，reduce用来缩减已有容器大小，resize重新设定容器大小。对于reduce和resize，需确保更改后的表空间有足够空间，否则DB2拒绝操作。
alter tablespace tbs_data extend (file '/database/tbs_data/cont0' 10M)
12、如果表空间容器已经没有剩余空间，可通过add选项增加容器，当然必要的情况下也可以通过drop选项删除容器。对于add和drop操作会在容器间发生数据重新平衡(rebalance)。如果数据量很大，rebalance时间有可能很久，对系统可能造成很大影响。

alter tablespace tbs_data add(file '/database/tbs_data/cont2' 100m)
13、如果使用begin new strip set选项则会在现有容器空间用完时使用新增容器，该选项使数据不会在容器间做rebalance，但会造成数据偏移。
alter tablespace tbs_data begin new stripe set (file '/database/tbs_data/cont3' 100m)

14、查看自动存储管理表空间路径
db2pd -d testdb -storagepaths
15、对于自动存储管理表空间，无法在表空间级进行容器更改，只能在数据级别，因为自动存储路径是在建库时指定的。可以使用add storage on选项为数据库添加新的存储路径。在v9.7版本之前，自动存储路径只能增加，不能删除。新增加的存储路径不会备表空间立即使用，只有在已有存储路径文件系统空间满了，才会使用新增路径，增加路径只是为了解决容量问题。v9.7版本之后，只要对使用自动存储管理的表空间执行rebalance，则该表空间可以立即使用新增存储路径。

alter database testdb add storage on '/database/czm'
alter tablespace 7 rebalance

16、将DMS表空间转换成自动存储管理表空间
alter tablespace tbs_data managed by automatic storage
alter tablespace tbs_data rebalance

参考至:《DB2数据库最佳管理实践》徐明伟著
http://www.ibm.com/developerworks/cn/data/library/techarticles/0212wieser/0212wieser.html
             http://www.dblotus.com/?p=436
             http://book.51cto.com/art/200906/129071.htm
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]