Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Set and alter Default value on huge table

    Hi Guru's

    We have DB2 9.7 as the database.

    Need to add a new column to a huge table (almost 1 billion history records in this fact table). The value on the column should be 1 for 90% of the records.

    My approach is adding the column with set default 1. Later alter the column default and set to NULL (in order to handle ongoing process / logic).

    Got few questions like

    a. Will it affect performance of the DB when set default to 1 and later to NULL
    b. Do we need to do RE-ORG on the table, if yes, when?
    c. Any other better approach ?

    Appreciate your thoughts on this!

    TYVM,
    B

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    a) It will only affect performance when the column is added with default = 1, since it needs to set it to all of the existing rows. Changing it later to null will not be a problem.

    b) It depends on what adding the column does to the pages. If a lot of them get overflowed, then yes you should reorg.

    c) I really do not see anything better.

    Andy

  3. #3
    Join Date
    May 2011
    Posts
    5

    Set and alter Default value on huge table

    Thanks Andy !

    "a) It will only affect performance when the column is added with default = 1, since it needs to set it to all of the existing rows."

    Does it take long time to complete this job?

    Usually, add column DDL would be quick (though I tried only on small tables) and set it with default value.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    actually when you add a column and set a default, it does not get populated on each row of the table. There is a marker put in the catalog. The value does not get put onto the individual rows until you perform a reorg.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •