IBM Data Server DB2 - Manual (Page 259)

Chapter 5. Deploying pre-configured databases 245
Figure 5-4 illustrates the changes we want to apply to the table itso.staff in our
sample database. We add the AWARDS column and change the data type of
column DEPT from smallint to integer.
Figure 5-4 Alter the table itso.staff in the sample database
Because we change the data type of the DEPT column, we cannot use the alter
table command. We are not able to use altobj either, because we want to add the
AWARD column as column number two, which is not supported by altobj — at
least not when there are data in the table. Example 5-21 shows how to make the
changes using a custom script.
Example 5-21 Custom script to change itso.staff
CREATE TABLE "ITSO"."SHADOWTABLE" (
"ID" SMALLINT NOT NULL ,
"AWARDS" SMALLINT NOT NULL,
"NAME" VARCHAR(9) ,
"DEPT" INTEGER ,
"JOB" CHAR(5) ,
"YEARS" SMALLINT ,
"SALARY" DECIMAL(7,2) ,
"COMM" DECIMAL(7,2) )
IN "ITSO2";
INSERT INTO "ITSO"."SHADOWTABLE" ("ID", "AWARDS", "NAME", "DEPT", "JOB",
"YEARS", "SALARY", "COMM")
SELECT "ID", 0, "NAME", "DEPT", "JOB", "YEARS", "SALARY", "COMM" FROM
"ITSO"."STAFF";
DROP TRIGGER "ITSO"."DO_NOT_DEL_SALES";
ALTER TABLE "ITSO"."STAFF" DROP CONSTRAINT "C_JOB";
Current
Configuration
New
Configuration
Delta