1

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

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?

  • I have checked it, already been a month after detaching paritions still unable to drop the detached partitions. pls help – Murali Chava Jan 20 '14 at 23:14
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.