27

create table department(dept char(3) not null,
                       deptname char(20) not null,
      constraint dept_name
      unique (deptname),
      primary key (dept));

create table employee(empno char(6) not null,
                      name char(30),
     age int,
     wkdept char(3) not null,
     constraint dept
     foreign key(wkdept)
     references department (dept) on delete cascade,
     constraint age check(age<200));

[db2inst1@]# db2 "alter table t1 add primary key(c4)"
[db2inst1@]# db2 "alter table albums primary key(itemno) add constraint fkartno foreign key(artno) references artists on delete cascade"
[db2inst1@]# db2 "alter table t1 add unique(c4)"
[db2inst1@]# dn2 "alter table stock add constraint cctype check(type in ('D','C','R'))"
对表结构更改时，会导致表状态异常 reorg-pending状态，需要reorg.
alter table t1 alter tid set data type number;
alter table t1 alter tid set not null;
alter table t1 drop column tid;

查看表占用空间大小
[db2inst1@]# db2 "select substr(tabname,1,32) as tabname,DATA_OBJECT_L_SIZE,DATA_OBJECT_P_SIZE,DICTIONARY_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME = 'EMP'"

select substr(tabname,1,32) as tabname,DATA_OBJECT_L_SIZE,DATA_OBJECT_P_SIZE,DICTIONARY_SIZE 
FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME = 'TB_AREA_ID'

ALTER TABLE TB_AREA_ID COMPRESS YES

REORG TABLE TB_AREA_ID   -- 重组
reorg table tableName resetdictionary

db2 alter table tablenam compress no；

db2 reorg table tablenam resetdictionary；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12961536/viewspace-1061384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12961536/viewspace-1061384/