IBM Support

LI74223: 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

  • Users Affected: All user prior to DB2 Version 95 Fix Pack 5.
    Problem Description: PCTFREE PARAMETER GET RESET AFTER 'ALTER
    TABLE...DROP COLUMN' IF STATISTICS WERE COLLECTED BEFORE.
    Problem was first fixed in DB2 Version 95 Fix Pack 5.
    

Problem conclusion

  • Problem was first fixed in DB2 Version 95 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI74223

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-09

  • Closed date

    2010-03-02

  • Last modified date

    2010-03-02

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

    LI74222

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 March 2010