IBM Data Server DB2 - Manual (Page 258)

244 DB2 Deployment Guide
years smallint ,
salary decimal (7, 2) ,
comm decimal (7, 2)) IN itso2',
-1, ? );
Note that if the newly added column is added in the middle of the table and the
table already has data, altobj might fail in loading the data, or the data loaded is
incorrect. Use the STAFF table shown in Example 5-20 as an example, if the JOB
is a newly added column. When altobj loads the data, it brings the data from the
old table and loads it on a column-by-column basic. The data in the YEARS
column will be loaded into the JOB column. Since the data type is different, the
load will fail. If the columns happen to have the same data type, the load job
succeeds, but the content of the data loaded is incorrect. If you want to add
columns between existing columns, use a custom script.
Using a custom script
One of the problems you have to address when using custom scripts for altering
a table is the object dependencies. DB2 will not allow you to make changes to a
table that has some specific dependencies on it — for instance, changing a table
that has a foreign key relation.
The strategy that we use in altering a table by script is to create a shadow table
of the table to be altered. Once the shadow table is created and loaded with data
from the original table, we drop the original table and rename the shadow table.
The detailed steps are as follows:
1. Create the shadow table with the new layout.
2. Add primary keys and indexes to the shadow table, using temporary names.
3. Copy data from the original table to the shadow table.
4. Remove non-data dependencies, such as stored procedures, constraints,
and so on from the original table.
5. Drop the original table.
6. Rename the shadow table to the original table name.
7. Rename objects created in step 2; this is necessary.
8. Add non-data dependencies, such as stored procedures, constraints, and so
on to the new altered table. Note that these dependencies are not necessarily
the same as those being removed in step 4, as these dependencies might
have changed as well.
Basically, this is also what the stored procedure altobj does. However, the
customized script will not have the limitations. You are, for instance, capable of
inserting a column at a specific location in a table.