IBM Support

LI74222: PCTFREE PARAMETER GET RESET AFTER 'ALTER TABLE...DROP COLUMN' IF STATISTICS WERE COLLECTED BEFORE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

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

Local fix

  • 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
    

Problem summary

  • In DB2 V91 and V95, value of PCTFREE for Table and INDEX gets
    resetted 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.
    

Problem conclusion

  • Problem is first fixed in DB2 V91 FP8.
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI74222

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-09

  • Closed date

    2009-10-05

  • Last modified date

    2009-10-05

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    LI74223 IC63970

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R910 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 October 2021