DB2 自动存储表空间（Automatic Storage）增加和删除存储路径

本文举例说明修改DB2自动存储表空间storage group的办法，如果您对storage group还不了解， 请先参考链接。

原本storage group有path1和path2，目标增加一条路径path3,并删掉path2，测试如下：

$ db2 "CREATE STOGROUP sg ON 'C:\test\path1', 'C:\test\path2' "

$ db2 "create tablespace tbstest managed by automatic storage USING STOGROUP sg"

$ db2 "create table t1(id int) in tbstest"

$ db2 "insert into t1 values(100),(200)"

查看修改操作会对哪些表空间产生影响:

$ db2 "SELECT substr(TBSP_NAME,1,30) as  TBSP_NAME  FROM table (MON_GET_TABLESPACE('', -2))  WHERE TBSP_USING_AUTO_STORAGE = 1  AND TBSP_CONTENT_TYPE IN ('ANY','LARGE')  AND STORAGE_GROUP_NAME = 'SG'  ORDER BY TBSP_ID"

TBSP_NAME
------------------------------
TBSTEST

  1 record(s) selected.

增加path3, 删除path2，删除之后，path2被会置为drop pending，再次删除的话就会报错SQL2092N:

$ db2 "alter STOGROUP sg add 'C:\test\path3'"

$ db2 "alter STOGROUP sg drop 'C:\test\path2' "
SQL2095W  Storage path "C:\test\path2" is in the drop pending state because
one or more automatic storage table spaces reside on the path.  SQLSTATE=01691

$ db2 "alter STOGROUP sg drop 'C:\test\path2' "
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL2092N  Storage path "C:\test\path2" is in the drop pending state. The
current request cannot be processed.  SQLSTATE=55073

最后需要一个Rebalance操作

$ db2 "alter tablespace tbstest rebalance"

$ db2 "alter STOGROUP sg drop 'C:\test\path2'"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1051N  The path "C:\test\path2" does not exist or is not valid.
SQLSTATE=57019

说明：如果是临时表空间，无法Rebalance, 需要删掉并重建临时表空间。
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0058603.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054957.html