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

来源：互联网发布：pr软件图标编辑：程序博客网时间：2024/06/18 07:38

本文举例说明修改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 becauseone 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 avalid Command Line Processor command.  During SQL processing it returned:SQL2092N  Storage path "C:\test\path2" is in the drop pending state. Thecurrent 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 avalid 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

0 0