IBM DB2 manual 245, Example 5-21 Custom script to change itso.staff

Figure 5-4illustrates 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.

New	Current
Configuration	Configuration
	Delta

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-21shows 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";

Chapter 5. Deploying pre-configured databases 245