I am getting the below error when I try to drop a tablespace. Can anyone help to resolve this error...

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.

But the above mentioned "table-name" does not contain any any part in different tablespace.

The message reference states:

"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".

Are you sure that no indexes or LOBs for that table are stored in other tablespaces?

YES...

I am damn sure that no db objects are occupying the other table space...

Thanks

If you are very confident, then ,

1) Check db2diag.log file ... There may be error messages. If there are none, set the diaglevel to 4 and issue the command again ... this time there shoud be messages.

2) If you are desperate to drop the tablespace, then contact IBM support with your problem . They should be able to help

If you want to check for dependencies once again, please read on ...


1) If you are comfortable with graphical tools, then in the control centre, tablespaces , right click on the tablespace name and select 'Show Related Objects'

2) If you are comfortable with the command line, then do

select * from syscat.tables where tbspace=<name> or index_tbspace=<name> or long_tbspace=<name>

Cheers
Sathyaram

You should use DB2DART to mark the tablespace as bad and put into drop pending mode to drop the tablespace. Be careful though and work with IBM support when using DB2DART.

Why not select from the catalog to prove there aren't any other objects?

If this is a dms tablespace it is possible that indexes are in another tablespace. Another way to check this out may be to extract the DDL for the table

db2look -e -p -d <dbname> -t <tabname> -x -z <schemaname> -o <outfile>

The other thing that may be the case is Foreign key relationships - this will prevent the drop - ie if you are trying to drop a parent while a child exists.