db2的DMS表空间管理主要有Exent/Reduce/Resize/Add/begin new stripe set,下面分别对这几项进行介绍
Exent扩展已有容器
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 3200
Useable pages = 3168
Accessible = Yes
SQL> select 3200*32/1024 from dual;
3200*32/1024
------------
100
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data extend (file
'/home/db2inst2/dms/tbs_data01.dbf' 20M)"
DB20000I The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 3840
Useable pages = 3808
Accessible = Yes
SQL> select 3840*32/1024 from dual;
3840*32/1024
------------
120
Reduce缩减已有容器
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data reduce (file
'/home/db2inst2/dms/tbs_data01.dbf' 40M)"
DB20000I The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 2560
Useable pages = 2528
Accessible = Yes
SQL> select 2560*32/1024 from dual;
2560*32/1024
------------
80
Resize重设容器大小
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data resize (file
'/home/db2inst2/dms/tbs_data01.dbf' 30M)"
DB20000I The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 960
Useable pages = 928
Accessible = Yes
SQL> select 960*32/1024 from dual;
960*32/1024
-----------
30
Add增加新容器
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data add (file
'/home/db2inst2/dms/tbs_data02.dbf' 30M)"
DB20000I The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 960
Useable pages = 928
Accessible = Yes
Container ID = 1
Name = /home/db2inst2/dms/tbs_data02.dbf
Type = File
Total pages = 960
Useable pages = 928
Accessible = Yes
Add增加新容器后,会在容器件进行数据Rebalance
begin new stripe set增加新容器
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data begin new stripe set (file
'/home/db2inst2/dms/tbs_data03.dbf' 10M)"
DB20000I The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst2/dms/tbs_data01.dbf
Type = File
Total pages = 960
Useable pages = 928
Accessible = Yes
Container ID = 1
Name = /home/db2inst2/dms/tbs_data02.dbf
Type = File
Total pages = 960
Useable pages = 928
Accessible = Yes
Container ID = 2
Name = /home/db2inst2/dms/tbs_data03.dbf
Type = File
Total pages = 320
Useable pages = 288
Accessible = Yes
begin new stripe set当已有容器使用完之后,再使用新增加容器