IBM Data Server DB2 - Manual (Page 256)

242 DB2 Deployment Guide
5.5.2 Updating table objects
When it comes to updating the table objects, certain complications arise.
Because tables contain information that has to be preserved, you cannot just
drop and recreate them. These are some of the complications:
Changing a table might increase the row size to an extent that it will no longer
fit in the existing table space. Because you cannot change the table space for
a table, you are required to create a new table in a larger table space and
then copy data from the old table. With some intermediate renaming, this is
possible.
Changing the type of a column. To change the type for a given column, type
conversion is required but it might not be simple.
Shortening fields. If a field has been shorten, you must ensure that the
existing data fits in the new column. For instance, changing the type from a
int
to a short or shortening the length of a character field.
Check constraints. If a check constraint is added or changed, you must
ensure that the existing data obey this new rule.
Preparing data
Before getting to the point where you can change a table, you have to ensure
that the data in the table will fit in the new layout. For instance, obeying check
constraints and fitting into the shortened column length are some of the issues
mentioned. First step in migrating a table might therefore be running a script to fix
the data. This might itself invoke some changes to the table. For instance, if a
check constraint is changed, then you usually have to remove the old constraint
up front before you can change the data. The new constraint is then added at the
end.
Altering the table
Once the data is prepared, it is ready to change the table. We roughly
categorized the changes as simple and complex changes. Simple changes are
those that can be applied by using the ALTER TABLE statement. For the
complex changes, we either use the stored procedure
altobj provided by DB2 or
use custom scripts.
Using the ALTER TABLE statement is straightforward, just run the statements. In
DB2 9, the ALTER TABLE ... DROP COLUMN and ALTER TABLE ... ALTER
COLUMN ... SET NOT NULL allow you to change the table layout easily. A reorg
is required after that. New functionality are frequently added to the ALTER
TABLE statement. Check the documentation for your current version of DB2 to
see whether the ALTER TABLE statement can fulfill your requirements. Use this
command whenever possible.