IBM DB2 Manuel d'utilisateur télécharger pdf (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

1 2 ... 254 255 256 257 258 259 260 261 262 263 264 ... 297 298

Pas de commentaire

IBM DB2 Manuel d'utilisateur Page 259