IBM manual Using a custom script, 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-20as 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.

244DB2 Deployment Guide