db2 表空间

一、表空间的作用

1、表空间是用户逻辑对象的存储空间
2、表空间在物理层上对应着若干个容器
3、容器可以是目录、文件或者裸设备
4、表空间是逻辑层与物理层的桥梁
5、数据库可以有多个表空间
6、而表空间可以有多个容器

二、表空间的类型

1、目录表空间
-存储DB2系统编目，即"数据字典"
-默认名称：syscatspace
2、系统临时表空间
-用于存储分组、排序、连接、重组、创建索引等操作的中间结果
-数据库至少有一个这样的表空间
-默认创建的名称：temspace1
3、用户临时表空间
-默认不会创建
-存放使用DECLARE GLOBAL TEMPORARY TABLE或CREATE GLOBAL TEMPORARY TABLE语句创建的表的临时数据
4、用户表空间
-存储用户对象（表、索引等）的空间
-默认创建的名称：userspace1

三、表空间存储的类型

数据库支持三种存储类型的表空间
系统管理的表空间（System-Managed Space，SMS）
数据库管理的表空间（Database-Managed Space,DMS）
自动存储的表空间（Automatic Storage With DMS）
三种存储的表空间可以共存于同一个数据库

1、SMS表空间

表空间的容器是操作系统的目录或文件夹
空间的分配和管理由操作系统的文件系统来完成
提供的优化选项比较少切性能不好

2、DMS表空间

表空间容器可以使用文件或者裸设备
存储空间的分配和管理由数据库来完成
DMS表空间可以被定义为大型（缺省）或者常规表空间

3、自动存储管理的表空间

数据库管理器负责创建和扩容容器
将存储的管理由表空间层面转入数据库层面
简化了表空间存储的管理
自动存储实际上不是一种单独的表空间类型，而是一种处理 DMS 存储的不同方式。DMS 容器需要比较多的维护，在 DB2 V8.2.2 中引入了自动存储
，作为简化空间管理的方式。

4、表空间大小的限制

表空间的大小是有上限的
表空间的页：4k，8k，16k，32k

四、创建表空间

1、创建表空间的语法

----创建DMS表空间
CREATE TABLESPACE name MANAGED BY DATABASE USING (FILE 'path' size)
eg.
db2 "create regular tablespace  tablespace_name  pagesize 32k managed by database using(file 'file_path' 60g) 
bufferpool bufferpool_name"
--bufferpool bufferpool_name 为表空空间指定缓存池
----为用户赋予表空间的权限
db2 "grant use of tablespace tablespace_name to user user_name"
----修改表空间设置自增(生产环境尽量不要设置自增)
db2 alter tablespace tablespace_name autoresize yes
----表空间的删除
/**
如果不删除与某个表空间相关联的所有表空间，那么不能删除该表空间。例如，如果您在一个表空间中创建了表并在另一个表空间中创建了其索引，
那么必须在一个 DROP TABLESPACE 语句中同时删除索引和数据表空间。
*/
 DROP TABLESPACE TABLESPACE_NAME
 
创建表空间
db2 "CREATE REGULAR TABLESPACE DMS_DBDATA IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32K MANAGED BY 
DATABASE USING (file '/data/DBDATA/rlvidx' 500M) EXTENTSIZE 32 PREFETCHSIZE AUTOMATIC BUFFERPOOL DATA_32K OVERHEAD 
7.500000 TRANSFERRATE 0.060000 DROPPED TABLE RECOVERY ON";
–PARTITION GROUP IBMDEFAULTGROUP ：使用分区组
–EXTENTSIZE：指定在跳到下一个容器之前可以写入容器PAGESIZE页面的数量。合理的EXTENTSIZE会对表空间的性能产生重大影响。这个参数只能
在创建表空间时定义，之后不能修改。
1） 如果小于50MB，EXTENTSIZE为8
2） 如果介于50MB到500MB之间，EXTENTSIZE为16
3） 如果介于500MB到5GB之间，EXTENTSIZE为32
4） 如果大于5GB，EXTENTSIZE为64
–PREFETCHSIZE AUTOMATIC：设置自动预取大小
查看是否设置自动预取大小： db2 get snapshot for tablespaces on dbname| more
–BUFFERPOOL DATA_32K 表空间使用的缓冲池
–OVERHEAD TRANSFERRATE 确定查询优化期间的I/O成本 单位毫秒 分别对应容器开销和传送速率平均值
授权表空间给用户(USERDB)：
db2 "grant use of tablespace DMS_DATE to user USERDB"
设置表空间自动扩展：
ALTER TABLESPACE DMS_XEFDATA AUTORESIZE YES
–查看表空间是否自动扩展
db2 pd -d dbName -tableSpace 其中AS为自动存储（yes/no） AR为自动扩展（yes/no）
增加表空间大小(在原有基础上增加1G空间)
db2 "alter TABLESPACE DMS_DATA extend (file '/data/DBDATA/rlvidx' 1G)"
增加临时表空间
db2pd -tablespaces -db mydb
db2 "select * from syscat.bufferpools"
db2 "create system temporary tablespace TEMPSPACE2 pagesize 32k managed by automatic storage bufferpool DATA_32K"

2、指定表空间类型的名称

LARGE创建大表空间
REGULAR创建常规表空间
SYSTEM TEMPORARY创建系统临时表空间
USER TEMPORARY 用户临时表空间
tablespace-name指定表空间的名称，不能重名和以"sys"开头

3、数据库分区组和数据页大小

DATABASE PARTITION GROUP db-partition-group-name为表空间指定数据库分区组
PAGESIZE指定表空间使用的数据页大小

4、表空间存储的管理方式和区大小

MANAGED BY指定表空间存储的管理方式
EXTENTSIZE 指定表空间区的大小

5、预取页的大小与缓冲池

PREFETCHSIZE指定预取页的个数
BUFFERPOOL指定表空间所使用的缓冲池

--获取数据库中存在的缓冲池名称列表
SELECT BPNAME FROM SYSCAT.BUFFERPOOLS
--创建缓存池(重启数据库才生效)
CREATE BUFFERPOOL buffer-pool-name PAGESIZE 4096
--创建缓冲池(8K)：
create bufferpool ibmdefault8k IMMEDIATE SIZE 5000 PAGESIZE 8 K ;
--创建缓冲池(16K)(OA_DIVERTASKRECORD)：
create bufferpool ibmdefault16k IMMEDIATE SIZE 5000 PAGESIZE 16 K ;
--创建缓冲池(32K)(OA_TASK)：
create bufferpool ibmdefault32k IMMEDIATE SIZE 5000 PAGESIZE 32 K ;
**定义用于缓冲池的页的大小。不带后缀 K 的整数的有效值为 4096、8192、16384 或 32768。后缀为 K 的整数的有效值为 4、8、16 或 32。
--数据库分区上创建缓冲池(如果指定了此参数，那么仅在这些数据库分区组中的数据库分区上创建缓冲池。每个数据库分区组当前必须存在于数据库
中。如果未指定 DATABASE PARTITION GROUP 子句，那么将在所有数据库分区上（以及后来添加至数据库的任何数据库分区上）创建此缓冲池。)
CREATE BUFFERPOOL buffer-pool-name PAGESIZE 4096
DATABASE PARTITION GROUP db-partition-group-name
--修改缓存池大小
ALTER BUFFERPOOL buffer_pool_name SIZE number of pages
*number of pages 是要分配给此特定缓冲池的新页数
--删除缓存池
DROP BUFFERPOOL buffer-pool-name

6、文件系统缓存和已删除表的恢复

NO FILE SYSTEM CACHING不使用文件系统的缓存特性
FILE SYSTEM CACHING使用文件系统的缓存特性
DROPPED TABLE RECOVERY设置是否启用已删除表的恢复特性

五、表空间维护

1、查看表空间

db2 list tablespaces [show detail]

2、查看表空间的容器

db2 list tablespace containers for tablespace_id [show detail]

3、查看表空间配置信息

db2pd -d database_name -tablespaces

4、通过snapshot 获取表空间信息

db2 get snapshot for tablespaces on database_name

5、修改表空间

alter tablespace tablespace_name

6、添加和扩容容器

db2 alter tablespace tablespace_name add (device '/dev/rhd1' 1000,device '/dev/rhd2' 1000)

7、更改表空间容器

对于DMS( database manage ) 表空间，提供了几个方法更改表空间容器。
    Add 用来增加新的容器
    Drop 删除容器
    Extend 扩展已有容器大小
    Reduce 缩减已有容器大小
    Resize 重新设定容器大小
Add和Drop操作，表空间容器之间会发生数据重新平衡(rebalance)，Reduce和Resize操作，需要确保修改后的表空间容器有足够的空间，否则DB2
  会拒绝该操作。
日常运维中出现表空间满的情况时，根据对存储空间和对运维的影响，有以下三种方案：
1)增加表空间、表容器，可通过alter tablespace 的 extend 或 resize 选项扩展已有表空间容器的大小。 
增加一个10000页的表容器文件
db2 " ALTER TABLESPACE PAYROLL ADD (DEVICE '/dev/rhdisk9' 10000) "
db2 " ALTER TABLESPACE IBSDATA ADD (DEVICE '/dev/ribsdata1' 10G,DEVICE '/dev/ribsdata2' 10G,DEVICE '/dev/ribsdata3'
10G,DEVICE '/dev/ribsdata4' 10G) " 
增加一个10G表容器文件，表空间的不同容器内会发生reblance
db2 "alter tablespace tbs_dat add ( file '/db2tbsp/dat_2' 10G)"
db2 => alter tablespace user2 add (file '/home/db2inst1/db2inst1/NODE0000/TEST1/container2' 10000)
使用新增加的容器。该选项不会在容器之间做reblance，不会对系统造成性能影响，但它会造成数据偏移。如下：
db2 "alter tablespace tbs_dat begin new stripe set (file '/db2tbsp/dat_2' 10G) "
2)修改表空间、表容器大小
给表容器扩展50GB：
db2 "alter tablespace tbs_dat extend ( file '/db2tbsp/dat_1' 50G, file '/db2tbsp/dat_2' 50G) ";
将相应的容器都扩大1000页，也就是增加1000页
db2 " ALTER TABLESPACE TS1 EXTEND (FILE '/conts/cont0' 1000, DEVICE '/dev/rcont1' 1000, FILE 'cont2' 1000) "
将原有的相应容器都改成大小是2000页
db2 " ALTER TABLESPACE TS1 RESIZE (FILE '/conts/cont0' 2000, DEVICE '/dev/rcont1' 2000, FILE 'cont2' 2000) "
把表空间中所有的容器大小都改成2000页
db2 "ALTER TABLESPACE TS1 RESIZE (ALL 2000)"
3)删除表空间tbs_dat的容器dat_2
db2 "alter tablespace tbs_dat drop ( file '/db2tbsp/dat_2' )"
仅为一个container缩减10个page
db2 => alter tablespace user2 reduce (file '/home/db2inst1/db2inst1/NODE0000/TEST1/container1' 10)
4）表空间容器的路径变化时，需要建立一个软链接(容器的原路径软链接到容器的新路径)，如：
ln -s /db2tbs/zdb/dat_1 /db2tbsp/dat_1

db2 alter tablespace tablespace_name extend (all 1000)
删除容器
db2 alter tablespace tablespace_name drop (file '/db2path1/datafile1')
重命名表空间
db2 rename tablespace_old to tablespace_new
删除表空间
db2 drop tablespace_name

8、降低部分表空间的高水位

示例 1：降低高水位标记并将所有容器减小 5 兆字节。以下示例降低表空间 ts 的高水位标记并将该表空间中所有容器的大小减小 5 兆字节。
   ALTER TABLESPACE ts LOWER HIGH WATER MARK
   ALTER TABLESPACE ts REDUCE (ALL CONTAINERS 5 M)
--注一般上面这个就够用了 
示例 2：降低高水位标记并将容器“Container1”缩小 2000 页。以下示例降低表空间 ts 的高水位标记并将“Container1”的大小缩小 2000 页。
   ALTER TABLESPACE ts LOWER HIGH WATER MARK
   ALTER TABLESPACE ts REDUCE (FILE "Container1" 2000)

六、表空间使用率

1. 统计所有节点表空间使用率
select substr(TABLESPACE_NAME,1,20) as TBSPC_NAME,bigint(TOTAL_PAGES * PAGE_SIZE)/1024/1024 as "TOTAL(MB)", 
used_pages*PAGE_SIZE/1024/1024 as "USED(MB)", free_pages*PAGE_SIZE/1024/1024 as "FREE(MB)" 
from table(snapshot_tbs_cfg('DB_NAME', -2)) as snapshot_tbs_cfg

2、查看表空间使用率
select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,
sum(tbsp_total_size_kb)/1024 as TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as 
FREE_MB,tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size
order by 1

3、通过存储过程查看数据库大小和容量
db2 call GET_DBSIZE_INFO (?,?,?,0)
　　  输出参数的值
　　  --------------------------
　　  参数名： SNAPSHOTTIMESTAMP
　　  参数值： 2008-05-26-10.53.06.421000
　　  参数名： DATABASESIZE
　　  参数值： 1203863552
　　  参数名： DATABASECAPACITY
　　  参数值： 4281493504
　　unit=byte, byte/1024/1024=m
　　db2 call GET_DBSIZE_INFO (?,?,?,0)
　　注意：
     a、从V8以上版本支持，此存储过程看数据库大小和容量；如果要看各表空间使用情况上面的sql方便，
　　 b、还有DPF环境下，db2 call GET_DBSIZE_INFO (?,?,?,0)只能看dbsize大小，无法看db的容量
 
4、查看数据库页大小
db2 get db cfg
5、收集表状态信息
db2 runstats on table schema.table_name
6、查询数据表占用页的数量
select tabname, npages from syscat.tables where tabname = 'table_name'
7、计算表占用磁盘空间大小
表占用磁盘空间大小 = 数据页大小 * 页数量
查看表占磁盘空间大小：select tabname, npages*16384/(1024*1024) from syscat.tables where tabname = 'XXXXXX'

七、示例

1、表空间创建示例

1、创建表空间最简单的命令
db2 create tablespace jikeimple
2、创建一个大型表空间
db2 create large tablespace jikelarge
3、创建一个用户临时表空间
db2 create user temporary tablespace jikeusertemp
4、创建一个8k页大小的表空间
db2 create tablespace jike8k pagesize 4k
5、创建一个数据库管理的表空间
db2 "create tablespace jikedms managed by database using (file '/dbpath/jikedms01.dbf' 10m ,file '/dbpath/jikedms02.dbf' 10m)"
6、创建缓冲池并创建表空间
创建buffer pool
db2 create BUFFERPOOL bigbuffer SIZE 4000 PAGESIZE 32K（可取数4k,8K,16K)
创建表空间
db2 "create tablespace newtbs04 pagesize 32k managed by system using ('
/home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000001/test.TMP') BUFFERPOOL bigbuffer"
newtbs04----代表表空间名
pagesize 32k 32可以指定为8k  16k  32k
bigbuffer---代表表空间名

7、

2、表空间操作示例

1、查看表空间
db2 list tablespaces
db2 list tablespaces show detail
2、查看表空间的容器
db2 list tablespace containers for 8
db2 list tablespace containers for 8 show detail
3、为表空间增加一个容器
db2 "alter tablespace jikedms add (file '/dbpath/jikedms03.dbf' 10m)"
4、修改容器的大小
db2 "alter tablespace jikedms resize (file '/dbpath/jikedms03.dbf' 5m)"
5、删除容器
db2 "alter tablespace jikedms drop (file '/dbpath/jikedms03.dbf')"
6、重命名表空间
db2 rename tablespace jikedms to myjikedms
7、删除表空间
db2 drop tablespace myjikedms

3、表空间、容器维护

表空间重置大小
db2 "alter tablespace GJDATA resize (FILE '/backup/GJDATA32K' 45G)"
容器路径 
db2 list tablespace containers for 8
容器大小  
db2pd -d uibsch -tablespaces
降低容器空间 resize  增加容器空间extend
首先，可通过 db2 list tablespaces show detail 来查看你的表空间状况。
表空间有二种类型：
1、SMS【SYSCATSPACE】，为系统管理表空间。
2、DMS，用户自己的表空间，为数据库管理表空间。
通过上面的命令来查看表空间中总的表空间(Total pages)和 剩余表空间(Free pages)，如果是系统管理表空间(SMS)不够，则是由于磁盘空间不
够，需要增加磁盘空间。如果是数据库管理表空间(DMS)不够，则需要扩展表空间。扩展表空间的命令为：
db2 alter tablespace extend (all )
例如：
db2 alter tabblespace yourtablespace extend(all 1024M)

db2pd -db dbname -tab
list tablespaces show detail
list tablespace containers for 2
alter tablespace userspace1 add ( device '/dev/rtablv' 750000)
ALTER TABLESPACE DMS_DATA ADD (File '/opt/IBM/db2/NODE0000/SQL00001' 327680)
1)直接添加一个容器的例子：
db2 " ALTER TABLESPACE PAYROLL ADD (DEVICE '/dev/rhdisk9' 10000) "
加容器之后DB2会有一个自动balance的过程
2)改变现有容器的大小(该方法不会触发balance,但如果表空间建立在裸设备上,则要扩冲裸设备空间):
db2 " ALTER TABLESPACE TS1 RESIZE (FILE '/conts/cont0' 2000, DEVICE '/dev/rcont1' 2000, FILE 'cont2' 2000) "
注意这种方式就是将原有的相应容器都改成大小是2000页
db2 "ALTER TABLESPACE TS1 RESIZE (ALL 2000)"
这种方式就是把表空间中所有的容器大小都改成2000页
db2 " ALTER TABLESPACE TS1 EXTEND (FILE '/conts/cont0' 1000, DEVICE '/dev/rcont1' 1000, FILE 'cont2' 1000) "
这种方式就是将相应的容器都扩大1000页，也就是增加1000页。
db2 " ALTER TABLESPACE DATA_TS EXTEND (ALL 1000)"
这种方式就是将所有的容器都增加1000页。
db2 " ALTER TABLESPACE USERSPACE1 EXTEND (ALL 100)"