Problem deleting detached partition in DB2 LUW V9.7

Question

Recently in prod environment we are having problems while dropping the detached partitions its giving this error

""" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL20285N The statement or command is not allowed because the table named "schemaname.tablename_JAN1" has detached dependents or the asynchronous partition detach operation is not complete. Reason code = "2". SQLSTATE=55057"""

I did some research and could find this from IBM saying we need to restart the instance to solve the issue. Link below I think you could have seen many of such incidents of what i described, please help me with this issue thanks in advance

http://www-01.ibm.com/support/docview.wss?uid=swg21515721

Answer 1

When you detach a partition from a table, the detach operation returns quickly, but DB2 then starts a background process to clean up any global indexes. You can't drop the table containing the detached partition until this process completes.

So, have you verified that the asynchronous cleanup that occurs after you issue the ALTER TABLE ... DETACH is actually finished?

Answer 2

As a suggestion, I would say that is good if you have all indexes for such table also 'partitioned'. Whan you detach a partion, it's easy to db2 to detach also the partition for indexes of rows that exists in partition table. And, also avoid create a Primary Key for that table. Create first an Unique index for column (or columns) that are PK, and, then alter table add constraint PK .... Next time, when you issue the alter table detach partition into , try to open another session to monitor the go through ... So, in another session issue the command: db2 list utilities show detail .... and see the detach still running. (or db2pd -db -utilities) hope it helps ...