DB2中修改表结构

张颢镡

1、增加一个字段
alter table table_name add column column_name datatype
例如：db2 "alter table SUPPORT_UAPROFILE add manufacturer varchar(20)"  
2、更改表得定义，将某个字段设为主键
alter table table_name add primary key (column_name)
3、把主键定义删除
alter table table_name drop primary key (column_name)
4、删除表中的字段
说明：db2数据库没有直接删除表中字段的SQL，但是可以通过多条SQL来删除表中的字段
步骤为：先创建一个临时表，将原表中的的数据插入到临时表中，然后将原表删除，把临时表改名为原名，然后增加其主键及其它约束。（注意：前提你必须了解到原表中包含的所有约束）
例子：
CREATE TABLE "DB2INST1"."TMP_PORTALSYSPARAM_DJPLH" (
  "PARAMNAME"VARCHAR(64)NOT NULL,
  "MODULEID"INTEGERNOT NULL,
  "MODULETYPE"INTEGERNOT NULL,
  "PARAMTITLE"VARCHAR(128)NOT NULL,
  "PARAMVALUE"VARCHAR(750)NOT NULL,
  "PARAMDESC"VARCHAR(250),
  "EFFECTIVETYPE"INTEGERNOT NULL,
  "VALIDATERULE"VARCHAR(1024)NOT NULL
)
  IN "USERSPACE1";

ALTER TABLE "DB2INST1"."TMP_PORTALSYSPARAM_DJPLH"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

INSERT INTO "DB2INST1"."TMP_PORTALSYSPARAM_DJPLH"( "PARAMNAME", "MODULEID", "MODULETYPE", "PARAMTITLE", "PARAMVALUE", "PARAMDESC", "EFFECTIVETYPE", "VALIDATERULE" )
SELECT "PARAMNAME", "MODULEID", "MODULETYPE", "PARAMTITLE", "PARAMVALUE", "PARAMDESC", "EFFECTIVETYPE", "VALIDATERULE"
FROM "DB2INST1"."PORTALSYSPARAM";

DROP TABLE "DB2INST1"."PORTALSYSPARAM";

RENAME TABLE "DB2INST1"."TMP_PORTALSYSPARAM_DJPLH" TO "PORTALSYSPARAM";

ALTER TABLE "DB2INST1"."PORTALSYSPARAM"
  ADD CONSTRAINT "PK_PORTALSYSPARAM" PRIMARY KEY
    ("PARAMNAME", "MODULEID", "MODULETYPE");


COMMIT;

RUNSTATS ON TABLE "DB2INST1"."PORTALSYSPARAM"
  AND INDEXES ALL
  ALLOW READ ACCESS;

COMMIT;