不太明白DB2的Alter table是怎么做的

zjsharp

对一个普通的table，增加一列。（环境:  DB2 UDB V8.2，OS：NT，客户端：Quest Central).
试验了两次, 从QuestCenter工具build的script不太一样, 想不明白为什么会这样?

A 增加一列:字符型,tmpCol varchar(40)
产生的script如下:
ALTER TABLE BACK.FAMILY
  ADD tmpCol VARCHAR(40);
#SYNC 10;

B 增加一列:数字型,tmpCol integer
产生的script如下:
#QSCOMMAND EXPORT TO '%DATA_PATH%\DWCTRLDB.BACK.FAMILY.25.1\FAMILY.ixf'
  OF IXF
  SELECT * FROM BACK.FAMILY;
#SYNC 10;

DROP TABLE BACK.FAMILY;

#SYNC 20;
CREATE TABLE BACK.FAMILY
("FAMILYID"      INTEGER         NOT NULL,
  "FAMILY"        VARCHAR(15),
  "FAMILY_ALIAS"  VARCHAR(25),
  tmpCol          INTEGER,
  "INTRODATE"     DATE
)
  DATA CAPTURE NONE
IN "USERSPACE1"
  PARTITIONING KEY
   (FAMILYID
   ) USING HASHING
  NOT LOGGED INITIALLY
  ORGANIZE BY DIMENSIONS
   ("FAMILYID"
   );

#SYNC 30;

ALTER TABLE BACK.FAMILY
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE
  LOG INDEX BUILD NULL;

#SYNC 40;

#QSCOMMAND IMPORT FROM '%DATA_PATH%\DWCTRLDB.BACK.FAMILY.25.1\FAMILY.ixf'
  OF IXF
  MODIFIED BY COMPOUND=5
              NOCHECKLENGTHS
  COMMITCOUNT 1000
--RESTARTCOUNT xxx
  INSERT
  INTO BACK.FAMILY
   (FAMILYID,
    FAMILY,
    FAMILY_ALIAS,
    INTRODATE
   );

#SYNC 50;

RUNSTATS ON TABLE BACK.FAMILY
  AND INDEXES ALL
  SHRLEVEL REFERENCE;
#SYNC 60;

后者给我的感觉是将表都全部重构了, 步骤这么多, 不知道为什么?
请教一下大家,谢谢

[ 本帖最后由 zjsharp 于 2006-11-11 09:43 编辑 ]

lklgdkp

V8.2的新功能，V8.2之前的版本是不能在alter table语句中增加column和修改column的属性的。现在的alter table使用存储过程把原来的表删除了，然后重新新建一个表。（估计是这样，没看过准确文档）

Changing generated column properties without having to
recreate the table
You can now use the ALTER COLUMN clause in the
ALTER TABLE statement to alter the various ways that a
column value can be generated.
Previously, generated expression or identity attributes
could be assigned to a column only when it was created.
Although the expression itself could be changed later,
once a generated column was created, it could not be
changed to a non-generated column without dropping and
then recreating the table. Before dropping the table, you
had to export the data in the table and then reload that
data into the recreated table with a redefined column.

[ 本帖最后由 lklgdkp 于 2006-11-13 16:24 编辑 ]

beginner-bj

这个问题比较怪，也许就是QuestCenter开发者的一个疏忽。