IBM Support

DROP or SELECT on a detached data partition from a partitioned table throws SQL20285N with Reason code 2

Technote (troubleshooting)


Problem(Abstract)

After detaching a data partition from a partitioned table using the ALTER TABLE statement with the DETACH PARTITION clause, DROP or SELECT on a detached data partition gives SQL20285N with Reason code = "2".

Symptom

Following error will be thrown in this case.

create table t1 (i1 int) partition by (i1) (starting 1 ending 10 every 2)
DB20000I The SQL command completed successfully.

insert into t1 values 1,2,3,4,5,6,7,8,9,10
DB20000I The SQL command completed successfully.

alter table t1 detach partition part0 into table t2
DB20000I The SQL command completed successfully.

select count(*) from t2
SQL20285N The statement or command is not allowed because the table named
"DB2ADMIN.T2" has detached dependents or the asynchronous partition detach
operation is not complete. Reason code = "2". SQLSTATE=55057

drop table t2
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
"DB2ADMIN.T2" has detached dependents or the asynchronous partition detach
operation is not complete. Reason code = "2". SQLSTATE=55057


Cause

The ABPDaemon may be killed or has stopped running.


Environment

DB2® Version 9.7 Fix Pack 1 and later releases.

Diagnosing the problem

Check for the following:

  1. The catalog view SYSCAT.DATAPARTITIONS reports for table shows 'L' STATUS forever, which indicates that there are no detached dependent tables.
  2. Using commit after detaching partition doesn't help
  3. "db2 list utilities show detail" returns no data
  4. Even after waiting for more time after DETACH, DROP and SELECT fails with same error.

    In such cases, collect "db2pd -abp dump all".

    1. Machine with problem shows like below:

      Date of dump: 2010-08-26-09.43.19.478745
      Control Block Address: 0x07000000D73CCE20
      Control Block Title: ABPDistributor
      Size of data being dumped: 10072

      ABPDistributor - 0x0000000110172CC0 (10072 bytes)
      m_daemonStarted = false
      m_daemonEDU = 0
      m_distributionQuiesced = false
      m_distributeWPTimeOut = 300000 ms


      NODE RECV SENT
      --------- --------- ----------
      0000 true false


    2. Machine without problem shows like below:

      Date of dump: 2010-08-26-08.54.24.557174
      Control Block Address: 0x0770000033888860
      Control Block Title: ABPDistributor
      Size of data being dumped: 10072

      ABPDistributor - 0x0000000110172CC0 (10072 bytes)
      m_daemonStarted = true
      m_daemonEDU = 7492
      m_distributionQuiesced = false
      m_distributeWPTimeOut = 300000 ms

      NODE RECV SENT
      ---------- --------- ---------
      0000 true true

Resolving the problem

The resolution to the symptom is to restart the instance before performing DROP or SELECT on the detached data partition.


To restart the instance, use the command db2stop and then db2start .

Related information

SQL20285N
Guidelines and restrictions on altering partitioned tab
Data partition detach phases
FP1: Detach operation for data partitions has been chan

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Tables

Software version: 9.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Enterprise Server

Reference #: 1515721

Modified date: 22 September 2011