索引和表不在一个表空间导致删除表空间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.