DB2 FOR LUW / DB2FORLUW / 970 - DB2

In DB2 V91 and V95, value of PCTFREE for Table and INDEX gets 
reset after 'ALTER TABLE...DROP COLUMN' if statistics were 
collected before. 
 
The index PCTFREE being reset to -1 and the table PCTFREE being 
reset to 0. 
 
Test scenario: 
 
CREATE TABLE aaa.test( x integer not null, y integer not null, z 
integer not null ) 
DB20000I  The SQL command completed successfully. 
 
alter table aaa.test pctfree 10 
DB20000I  The SQL command completed successfully. 
 
create index aaa.ind on aaa.test(x,z) pctfree 40 
DB20000I  The SQL command completed successfully. 
 
select pctfree from syscat.indexes where indschema='AAA' and 
indname='IND' 
 
PCTFREE 
-------                      <<<<<<<<<< 
     40 
 
  1 record(s) selected. 
 
insert into aaa.test with tmp(x,y,z) as ( values (1,1,1) union 
all select x+1, y+1, z+1 from tmp where x < 100 ) select * from 
tmp 
DB20000I  The SQL command completed successfully. 
 
runstats on table aaa.test with distribution and detailed 
indexes all 
DB20000I  The RUNSTATS command completed successfully. 
 
select pctfree from syscat.tables where tabschema='aaa' and 
tabname='TEST' 
 
PCTFREE 
------- 
     10                      <<<<<<<<<<<<< 
 
  1 record(s) selected. 
 
 
ALTER TABLE aaa.test DROP COLUMN y 
DB20000I  The SQL command completed successfully. 
 
select pctfree from syscat.tables where tabschema='aaa' and 
tabname='TEST' 
 
PCTFREE 
------- 
      0                           <<<<<<<<<<<< 
 
  1 record(s) selected. 
 
 
select pctfree from syscat.indexes where indschema='AAA' and 
indname='IND' 
 
PCTFREE 
------- 
     -1                          <<<<<<<<<<< 
 
  1 record(s) selected. 
 
At this stage, PCTFREE is 0 for table and -1 for index. We 
should have PCTFREE as 10 for table and 40 for index, even after 
'ALTER TABLE... DROP COLUMN'.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 UDB systems on all Linux, Unix, and Windows          * 
* platforms at service levels from Version 9.1 GA through      * 
* Version 9.5 and 9.7.                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* n DB2 V91 and V95, value of PCTFREE for Table and INDEX gets * 
*                                                              * 
* reset after 'ALTER TABLE...DROP COLUMN' if statistics were   * 
*                                                              * 
* collected before.                                            * 
*                                                              * 
* External symptoms include the index PCTFREE being reset to   * 
* -1                                                           * 
* and the table PCTFREE being reset to 0.                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to fix packs that contain the fix (as listed above)  * 
* or set the PCTFREE value manually via ALTER TABLE statement  * 
* (only work for table PCTFREE).                               * 
****************************************************************

1. For table, we need to alter the table after "Alter Table.. 
   Drop Column" to reflect PCTFREE value. 
 
   For eg: alter table aaa.test pctfree 10 
 
 
2. For index, we need to drop and rebuild the index with PCTFREE 
   value. 
 
   For eg: 
    drop index aaa.ind 
    create index aaa.ind on aaa.test(x,z) pctfree 40

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

Problem is first fixed in DB2 V9.1 FP8, V9.5 FP5 and V9.7 FP1.

not known / see Local fix

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

22.10.2009
23.12.2009
23.12.2009

9.1.FP8,
9.7.FP1