IBM manual Updating table objects, Preparing data, Altering the table, 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.

242DB2 Deployment Guide