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

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

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.

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.