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?
TsangAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
0
TsangAuthor Commented:
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.
0
Kent OlsenDBACommented:
On one hand, you've got "only" 10,000,000 rows.  I'd be tempted to just do it.

Another workable solution is to create a new table with the new definition, insert all of the data in the current table into the new table, delete the old table, and rename the new table.  That has the same impact as the ALTER (adding a column) and a REORG, but the table is unavailable for less than a second (between the DELETE and RENAME).


If you really want to just alter the current table, let's do some math.

You've got a table with three fields that are varchar(50), one is not nullable, and want to add a nullable field.

The shortest row would be ('A', NULL, NULL) requiring 5 bytes.

The longest row would be ('A...A', 'B...B', 'C...C') requiring 156 bytes.

Every row also contains a 6 byte header, so every row is between 11 and 161 bytes.  The new table would have rows between 12 and 162 bytes.  Assuming that you're using 4K pages (the default) you've got between 24 and 365 rows per block.  Your new size will have between 24 and 333 rows per block.  (Without knowing your data, it's impossible to know the exact sizes.)

So you've got between 27,400 paqes and 416,000 pages.  That's quite a gap, and every page will have to be read, each row modified, and each page rewritten.  Full pages would have to be split, growing the table/tablespace.

My best guess is that the ALTER would complete in a minute or two.  And the data would wind up somewhat "disjointed".  Depending on the typical data access, that may not be an issue.


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TsangAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.