DB2的自动存储管理非常类似ORACLE的OMF,只要设置好相关的存储路径,在创建表空间的时候就可以不用指定文件路径。
如下:
db2 => create tablespace tbsauto
DB20000I The SQL command completed successfully.
不过存储路径的空间不够了,我们需要增加另外的存储路径。
在DB2 V9.7之前 增加后的存储路径将不能删掉,而且新增加的存储路径需要在原来的空间用完后才能使用。
在DB2 V9.7之后 既可以增加也可以删除,而且新增加的存储空间对表空间执行重新平衡后就可以立马使用到。
如下所示:
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:02:21
Database Storage Paths:
Number of Storage Paths 1
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
目前只有一个存储路径/dbauto
[yansp@db2server ~]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = YANSP
Local database alias = TESTDB
[yansp@db2server ~]$ ls -lR /dbauto1
/dbauto1:
total 0
[yansp@db2server ~]$ db2 "alter database testdb add storage on '/dbauto1'"
DB20000I The SQL command completed successfully.
新增存储路径/dbauto1
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:03:43
Database Storage Paths:
Number of Storage Paths 2
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
0xA7A3E190 4 NotInUse /dbauto1
新增后的存储路径将处于 NotInUse 状态,必须对原来的表空间执行重平衡才能用到新的存储路径。
[yansp@db2server ~]$ db2 alter tablespace tbsauto rebalance
DB20000I The SQL command completed successfully.
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:04:11
Database Storage Paths:
Number of Storage Paths 2
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
0xA7A3E190 4 InUse /dbauto1
[yansp@db2server ~]$ ls -lR /dbauto1
/dbauto1:
total 4
drwx--x--x 3 yansp yansp 4096 Feb 18 23:10 yansp
/dbauto1/yansp:
total 4
drwx--x--x 3 yansp yansp 4096 Feb 18 23:10 NODE0000
/dbauto1/yansp/NODE0000:
total 4
drwx--x--x 3 yansp yansp 4096 Feb 18 23:10 TESTDB
/dbauto1/yansp/NODE0000/TESTDB:
total 4
drwx--x--x 2 yansp yansp 4096 Feb 18 23:10 T0000008
/dbauto1/yansp/NODE0000/TESTDB/T0000008:
total 32804
-rw------- 1 yansp yansp 33554432 Feb 18 23:10 C0000001.LRG
未执行重新平衡的其他表空间将不会用到新的存储路径。
当然了 新创建的表空间也可以用到新的存储路径。
db2 => create tablespace newtbs
DB20000I The SQL command completed successfully.
[yansp@db2server ~]$ ls -lR /dbauto1
/dbauto1:
total 4
drwx--x--x 3 yansp yansp 4096 Feb 18 23:10 yansp
/dbauto1/yansp:
total 4
drwx--x--x 3 yansp yansp 4096 Feb 18 23:10 NODE0000
/dbauto1/yansp/NODE0000:
total 4
drwx--x--x 4 yansp yansp 4096 Feb 18 23:13 TESTDB
/dbauto1/yansp/NODE0000/TESTDB:
total 8
drwx--x--x 2 yansp yansp 4096 Feb 18 23:10 T0000008
drwx--x--x 2 yansp yansp 4096 Feb 18 23:13 T0000009
/dbauto1/yansp/NODE0000/TESTDB/T0000008:
total 32804
-rw------- 1 yansp yansp 33554432 Feb 18 23:10 C0000001.LRG
/dbauto1/yansp/NODE0000/TESTDB/T0000009:
total 16404
-rw------- 1 yansp yansp 16777216 Feb 18 23:13 C0000001.LRG
也可以删除存储路径,如果存储路径已被使用,在删除后,存储路径将会处于删除暂挂状态。
必须对其下的表空间执行重平衡才能彻底删除掉。
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:08:34
Database Storage Paths:
Number of Storage Paths 2
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
0xA7A3E190 4 InUse /dbauto1
[yansp@db2server ~]$ db2 "alter database testdb drop storage on '/dbauto1'"
SQL2095W Storage path "/dbauto1" is in the drop pending state because one or
more automatic storage table spaces reside on the path. SQLSTATE=01691
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:08:56
Database Storage Paths:
Number of Storage Paths 2
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
0xA7A3E190 4 DropPending /dbauto1
[yansp@db2server ~]$ db2 alter tablespace tbsauto rebalance
DB20000I The SQL command completed successfully.
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:09:08
Database Storage Paths:
Number of Storage Paths 2
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto
0xA7A3E190 4 DropPending /dbauto1
[yansp@db2server ~]$ db2 alter tablespace newtbs rebalance
DB20000I The SQL command completed successfully.
[yansp@db2server ~]$ db2pd -d testdb -storagepaths
Database Partition 0 -- Database TESTDB -- Active -- Up 0 days 01:09:20
Database Storage Paths:
Number of Storage Paths 1
Address PathID PathState PathName
0xA47500C0 0 InUse /dbauto