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