asked on

Alter table without reorg

I have the following table T1, and I wanted to add a new column C4.

CREATE TABLE T1
(
C1 VARCHAR(50) NOT NULL,
C2 VARCHAR(50),
C3 VARCHAR(50),
CONSTRAINT PK_T1 PRIMARY KEY(C1)
);

alter table T1 add column C4 VARCHAR(50);

It seems reorg is not compulsory in this case. But I worry will this affect the performance of select/insert/update/delete on this table if reorg is not done? Or will there be other potential issues?

Kent Olsen

Hi Tsang,

No reorg should be necessary as column "C4" is "just data". A reorg disregards non-key data when restructuring the table.

That said, when the column is added, every column in your table automatically grows by 1 byte to accommodate the "nullable" flag for the new column. Depending on the data blocking, DB2 may have to move some rows if the total length of all rows in any block exceeds the block size. This also applies when a row is updated (and the length of the row grows) so your table is subject to that kind of process during normal use.

How many rows are in the table?

Kent

Tsang

ASKER

Hi Kdo,

Thanks your explanation.

So does it mean that, at the time I run the "alter table" command, each row will grow by 1 byte, and those that no longer fit in the blocks will be moved immediately? The reason I have to be so specific is that T1 is a large table, the size is of magnitude 10000000 to 100000000, I worry that it takes long time to complete the whole operation.

On the other hand, "update" is rare, and "delete" is done by batch job once a day at midnight when the traffic is low, so I guess the impact is limited.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tsang

ASKER

Thanks Kent.

We will change DB structure together when we deploy new project release, so all application will be stopped and there should be sufficient time for a few minutes.