索引和表不在一个表空间导致无法删除表空间SQL0282N 错误

一、故障现象
在删除老经分数据库的表空间时，删除表空间时报错：
[DWE3:/db2home/db2inst1/fengsh]db2 "drop tablespaces tbs_ods"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0282N  Table space "TBS_ODS" cannot be dropped because at least one of the
tables in it, "PODS.T_ODS_MRK_TOTAL_M1110", has one or more of its parts in
another table space.  SQLSTATE=55024
 
意思是PODS.T_ODS_MRK_TOTAL_M1110这个表在此表空间，而他至少有一或多个部分在其他表空间。
 
[DWE3:/db2home/db2inst1/fengsh]db2 ? SQL0282N

SQL0282N Table space "<tablespace-name>" cannot be dropped
          because at least one of the tables in it,
          "<table-name>", has one or more of its parts in another
          table space. 
Explanation:
A table in the specified table space does not contain all of its
parts in that table space.  If more than one table spaces are
specified, then a table in one of the specified table spaces does
not contain all of its parts in the list.  The base table,
indexes, or long data may be in another table space, so dropping
the table space(s) will not completely drop the table.  This
would leave the table in an inconsistent state and therefore the
table space(s) cannot be dropped. 
User Response:
Ensure that all objects contained in table space
"<tablespace-name>" contain all their parts in this table
space before attempting to drop it, or include those table spaces
containing the parts in the list to be dropped. 
 This may require dropping the table "<table-name>" before
dropping the table space. 
 sqlcode :  -282
 sqlstate :  55024

检查确认：
db2 "select tabschema,tabname,indschema,indname,a.TBSPACEID,TBSPACE from syscat.indexes a,syscat.tablespaces b where a.tbspaceid=b.tbspaceid and tabname='T_ODS_MRK_TOTAL_M1110'" 
TABSCHEMA                                                                                                                        TABNAME                                                                                                                          INDSCHEMA                                                                                                                        INDNAME                                                                                                                          TBSPACEID   TBSPACE                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------
PODS                                                                                                                             T_ODS_MRK_TOTAL_M1110                                                                                                            PODS                                                                                                                             SQL111103090750936                                                                                                                         6 TBS_IDX                                                                                                                        
  1 record(s) selected.
此表确实有一个索引建在TBS_IDX表空间。
 

二、处理过程
捞出此表空间上所有表，将表删除，索引自动删除后，直接删除成功
select substr(tabschema,1,20) as tabschema,substr(tabname,1,60) as tabname,'db2 drop table'||rtrim(tabschema)||'.'||rtrim(tabname)||';' as sql from syscat.tables t1,syscat.tablespaces t2 where t1.tbspaceid=t2.tbspaceid and t2.tbspace='TBS_ODS'
TABSCHEMA            TABNAME                                                      SQL                                                                                                                                                                                                                                                                            
-------------------- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PODS                 T_ODS_STL_SMS                                                db2 drop tablePODS.T_ODS_STL_SMS;                                                                                                                                                                                                                                              
PODS                 T_ODS_STL_VOC                                                db2 drop tablePODS.T_ODS_STL_VOC;                                                                                                                                                                                                                                              
PODS                 T_ODS_BAL_CDMA1X                                             db2 drop tablePODS.T_ODS_BAL_CDMA1X;                                                                                                                                                                                                                                          
 
[DWE3:/db2home/db2inst1/fengsh]db2 "drop tablespace tbs_ods"
DB20000I  The SQL command completed successfully.
[DWE3:/db2home/db2inst1/fengsh]db2 commit
DB20000I  The SQL command completed successfully.

三、总结
此问题主要是由于删除的表空间上表和表创建的索引不在同一表空间，解决的方法主要有两种：
1）删除表空间上所有表，再删除表空间
2）同时删除数据表空间和索引所在表空间（我们的环境由于索引表空间共用，还有其他表空间上表也在使用此索引表空间，无法删除）

例如Salesidx 和user 表空间。
db2 "drop tablespaces Salesidx , user"

删除 Salesidx
e91q3a@TSGDB2:/home/db2users/e91q3a> db2 drop tablespaces Salesidx
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0282N Table space "SALESIDX" cannot be dropped because at least one of the
tables in it, "E91Q3A.AABB", has one or more of its parts in another table
space. SQLSTATE=55024

删除user 表空间
e91q3a@TSGDB2:/home/db2users/e91q3a> db2 drop tablespaces user
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0282N Table space "USER" cannot be dropped because at least one of the
tables in it, "E91Q3A.AABB", has one or more of its parts in another table
space. SQLSTATE=55024

e91q3a@TSGDB2:/home/db2users/e91q3a> db2 "drop tablespaces Salesidx , user"
DB20000I The SQL command completed successfully.