[ DB2 ] DB2 칼럼 변경 안될 때 테이블 교체

1. 새 테이블 생성

create table NEW_TABLE
(
    IC_CODE            INTEGER generated always as identity primary key,
    IC_CONTENT         VARCHAR(50),
    IC_TYPE            VARCHAR(10),

    USEYN              VARCHAR(1)   default 'Y',
    REGDT              TIMESTAMP(6) default CURRENT TIMESTAMP not null,
    REGID              VARCHAR(10),
    UPDDT              timestamp not null generated always for each row on update as row change timestamp,
    UPDID              VARCHAR(10)
);

 

2. 데이터 복제

insert into NEW_TABLE (
    NEW_COLUMN,
    USEYN,
    REGDT,
    REGID
)
select
    OLD_COLUMN,
    USEYN,
    REGDT,
    REGID
from OLD_TABLE;

 

3. 데이터 확인

SELECT * FROM NEW_TABLE;

 

4. 기존 테이블 삭제

RENAME TABLE OLD_TABLE to DEPRECATED_OLD_TABLE;

 

5. 테이블명 변경

RENAME TABLE NEW_TABLE to TABLE;