db2 mysql oracle 表空间,DB2表空间管理(2)—DMS空间管理篇

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当已有容器使用完之后，再使用新增加容器