I've got a database with a large partitioned table where each
partition is in its own tablespace. We need to create a smaller test
version of this database where we don't need data from all the
partitions. I have a good full backup from this database, so I did a
"restore rebuild" including only the tablespaces I need. This worked
great. I then was able to detach the tablespaces with the good data
into their own tables.

What I wanted to do then was to drop the original table and recreate
it with a subset of the original partitions (attaching each partition
from the good tables I had managed to detach from the original
table).

So when I try to drop the original table, I get this error:

SQL0290N Table space access is not allowed. SQLSTATE=55039

Makes sense. So then I figure I'll just drop the tablespaces. Well,
that doesn't work either. I get this:

SQL0282N Table space "TEST" cannot be dropped because at least one of
the
tables in it, "TEST.T1", has one or more of its parts in another table
space. SQLSTATE=55024

If I try to detach those partitions, I get the same error as when I
try to drop the table. (Table space access is not allowed).

So, is there anyway to get rid of that table?

Thanks for any help.

Greig Wise

On Aug 20, 12:57*am, Greig <frital... (AT) gmail (DOT) com> wrote:
Hi Greig,

If you get SQL0290N you should check the state of your tablespaces.
What are the results of this query:

select tbsp_state, tbsp_name
from sysibmadm.snaptbsp_part
where tbsp_state<>'NORMAL'

Sincerely,
Mark B.

you may have your data/index on different tablespaces. try to drop them together.

db2 "drop tablespace a,b"

On Aug 22, 10:11*am, Yonghang Wang <wyh... (AT) gmail (DOT) com> wrote:
This took care of it. I didn't know you could drop multiple
tablespaces at once. Thank you.