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

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