DB2 FOR LUW / DB2FORLUW / 970 - DB2

This problem is hit in the following scenario: 
- The user defines a ranged partitioned table that uses default 
values (i.e. the WITH DEFAULT clause) for one or more of it's 
columns. 
- The user alters the table to ACTIVATE NOT LOGGED INITIALLY, 
then rolls back this transaction (either explicitly or 
implicitly).  At this point the table is invalid and can only be 
dropped. 
- The user then alters the table to detach a partition. 
 
If this happens, the partition is placed into 
online-detach-pending state.  However the online detach can 
never complete because the table is not available.  As a result 
the table is still not available and can no longer be dropped. 
 
All of the following symptoms will be observed: 
 
1.  Any attempt to access of the partitioned table will return 
SQL1477N 
2.  Any attempt to access the target table from the detach will 
return SQL20285N

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users using detach from a range partition table              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* While trying to detach a partition from a range partition    * 
* table the partition is placed into online-detach-pending     * 
* state.                                                       * 
* Any  attempt to access the partitioned table  will return    * 
* SQL1477N and any attempt to access the target table from the * 
* detach will return SQL20285N                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* To avoid experiencing this issue users can upgrade to DB2    * 
* Version V9.7 fix pack 5 at the minimum.                      * 
****************************************************************

1.  Restore from a full database backup. 
2.  Contact DB2 support and they will help you use a db2dart 
support option to mark the table as available so that the 
asynchronous partition detach can complete.  The table should 
then be dropped.

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First Fixed in DB2 Version V9.7 fix pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

26.07.2011
28.12.2011
28.12.2011

9.7.FP5