[SQL] 변경된 데이터 추적 및 V10의 신기능 시간 테이블 (Temporal Table)

DBMS의 기능 요건을 살피면 “사용자 실수에 의한 트랜잭션 복구” 요건을 보게 되곤 한다.

해당 요건은 ORACLE의 Flashback 기능을 이용한 “행 레벨”, “테이블 레벨” 혹은 “DB 레벨”의 시점 복구를 의미한다.

 

SQL 수행을 잘못해서 취소를 하고자 할 경우 oracle은 flashback을 통해서 SQL 수행 전 시점으로 되돌 릴 수 있다.

현재에도 DB2 에서는 해당되는 기능이 없는 것으로 알고 있기에  “어떤 대안이 있을까” 하는 고민을 하게 된다.

 

테이블공간 생성 시 (현재는 기본 옵션이지만) “삭제된 테이블 복구” 옵션을 주는 경우 삭제된 테이블의 DDL 추출과 백업 이미지를 통하여 데이터를 추출해 내어 복구 작업을 할 수 있다.

이외 Optim HPU(High Performance unload) 유틸리티를 통해서도 백업 이미지에서 특정 데이터를 추출이 가능해서 지워진 데이터 복구가 백업 시점까지는 가능하다.

 

만일 DB2에서 데이터가 변경된 시점의 데이터를 원래대로 되돌리고 싶다면?

데이터 변경 SQL 작업 시 변경 데이터를 별도로 저장을 하거나, V10에서 제공하는 시간 테이블을 통하여 데이터를 추출해서 변경하는 방법이 있을 듯 하다. 단, 명령어 한 줄로 간단히 수행이 아닌,  건건이 update, insert 문을 통해서 작업을 해야 되기 때문에 많이 번잡스러울 것 같다.

 

1) SQL를 통한 로그 작업

    데이터 변경 SQL에 대해서는 다음과 같이 SQL을 사용하여 “데이터 변경 이력”을 관리할 수 있을 것이다.

 

테스트 용 테이블

db2 "create table t1 (i integer, t timestamp, v varchar(10), c varchar(20))" db2look -d sample -e -t t1 (테이블 DDL확인)

 

변경 전 데이터 조회

데이터 입력 db2 "insert into t1 values (4, current timestamp, 'DDD', 'final') " 데이터 변경하면서 변경 전 값 확인 (DDD –> DEF) db2 "select * from old table (update t1 set v='DEF' where v='DDD')"

 

변경 전 값 결과

I           T                          V          C ----------- -------------------------- ---------- --------------------           4 2013-02-14-11.05.49.619950 DDD        final

 

변경 후 데이터 조회 (DDD –> DEF)

db2 "select * from new table (update t1 set v='DEF' where v='DDD')"

 

변경 후 데이터

I           T                          V          C ----------- -------------------------- ---------- --------------------           4 2013-02-14-11.05.49.619950 DEF        final

 

삭제된 데이터 조회

db2 "select * from old table (delete from t1 where v='DEF')"

 

결과 값

I           T                          V          C ----------- -------------------------- ---------- --------------------           4 2013-02-14-11.05.49.619950 DEF        final   db2 "select * from t1" I           T                          V          C ----------- -------------------------- ---------- --------------------           1 2013-02-14-11.00.23.925648 AAA        before           2 2013-02-14-11.00.28.735184 BBB        before           3 2013-02-14-11.00.34.503851 CCC        before   3 record(s) selected.

 

데이터 변경 이력을 관리하고자 한다면 위와 같이 SQL을 사용함으로서 가능할 듯 하다.

단 별도로 변경 이력을 관리하기 위한 저장소가 필요하므로 번잡스럽다.

 

이것을 조금 더 개선시킨 것이 V10에서 나온 Temporal Table 인 것 같다.

 

 

2) 시간 테이블을 통한 데이터 변경 이력 관리

   “시간” 중심으로 데이터 변경 이력을 관리하도록 만들어진 테이블로, “시간”을 “시스템” 혹은 “사용자 정의”  ,” 시스템+사용자 정의” 기준으로 이력 관리를 할 수 있도록 제공한다.

 

    “시스템 시간” 및 “사용자 정의 시간”(Business_Time) 은 특수 Register 변수를 통해서 설정이 가능하다.

 

설정 예

SET CURRENT TEMPORAL BUSINESS_TIME = CURRENT TIMESTAMP - 1 MONTH SET CURRENT TEMPORAL BUSINESS_TIME = NULL SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 MONTH

 

위 설정으로 current date, current timestamp 값이 영향을 주지 않으면, 트잭잭션 rollback 시에도 영향을 주지 않는 것으로 정보 센터에는 언급되어 있다.

(참고) http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0057360.html

 

(테스트) 시스템 시간 기준 데이터 이력 관리

db2 "create table t1 (i integer, t timestamp, v varchar(10), c varchar(20), sys_start timestamp(12) not null generated always as row begin, sys_end timestamp(12) not null generated always as row end, ts_id timestamp(12) not null generated always as transaction start id, period system_time(sys_start,sys_end))"

 

이력 테이블 생성

db2 "create table t1_hist (i integer, t timestamp, v varchar(10), c varchar(20), sys_start timestamp(12) not null, sys_end timestamp(12) not null, ts_id timestamp(12) not null)"

 

이력테이블 연결 작업

db2 "alter table t1 add versioning use history table t1_hist"

 

데이터 입력

db2 "insert into t1 values (1, current timestamp, 'AAA', 'before')"

(참고) 데이터 입력 후, 이력 테이블(t1_hist) 를 조회하면 값이 출력되지 않는다.

         저장된 데이터의 변경(삭제 포함) 이력만 관리하기 때문이다.

db2 "select * from t1" I           T                          V          C                    SYS_START                        SYS_END                          TS_ID ----------- -------------------------- ---------- -------------------- -------------------------------- -------------------------------- --------------------------------           1 2013-02-14-13.29.20.186109 AAA        before               2013-02-14-13.29.20.186107000000 9999-12-30-00.00.00.000000000000 2013-02-14- 13.29.20.186107000000 db2 "select * from t1_hist"

 

데이터 변경 (AAA –> ABC)

db2 "update t1 set v='ABC'" db2 "select * from t1" I           T                          V          C                    SYS_START                        SYS_END                          TS_ID ----------- -------------------------- ---------- -------------------- -------------------------------- -------------------------------- --------------------------------           1 2013-02-14-13.29.20.186109 ABC        before               2013-02-14-13.31.03.853468000000 9999-12-30-00.00.00.000000000000 2013-02-14- 13.31.03.853468000000 db2 "select * from t1_hist" I           T                          V          C                    SYS_START                        SYS_END                          TS_ID ----------- -------------------------- ---------- -------------------- -------------------------------- -------------------------------- --------------------------------           1 2013-02-14-13.29.20.186109 AAA        before               2013-02-14-13.29.20.186107000000 2013-02-14-13.31.03.853468000000 2013-02-14- 13.29.20.186107000000

 "붉은 색" 글씨는 변경된 데이터 시점의 시간을 의미한다.

 

* 사용자 정의 시간 기준 이력 관리 테이블(Application-period temporal tables)

   시스템과는 다르게 “generated always” 옵션이 제외되었고, 사용자가 지정하여 시간을 넣기 때문에 중복 값이 발생하지 않도록 “고유 키” 를 생성한다.

create table 테이블명 ( 컬럼명 컬럽타입, bus_start DATE NOT NULL, bus_end DATE NOT NULL, period business_time (bus_start, bus_end)) create unique index idx_테이블명    on 테이블명 (컬럼명 컬럼타입, business_time without overlaps);

 

 

* 시스템 + 사용자 정의 시간 기준 이력 관리 (Bi-temporal table)

db2 +p -tv << EOF create table t1 (i integer, t timestamp, v varchar(10), c varchar(20),                  bus_start  DATE NOT NULL,                  bus_end    DATE NOT NULL,                  sys_start  timestamp(12) not null generated always as row begin,                  sys_end    timestamp(12) not null generated always as row end,                  ts_id      timestamp(12) not null generated always as transaction start id,                  period business_time (bus_start, bus_end),                  period system_time(sys_start,sys_end)) ; create table t1_hist like t1 ; alter table t1 add versioning use history table t1_hist; create unique index idx_t1 on t1(i,business_time without overlaps) ; EOF

 

(참고) http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0058476.html

 

데이터 이력관리는 “데이터 변경 작업이 발생하는” 업무적으로 중요한 테이블이 대상이 되어야 할 것이다.

속성 상, 데이터가 많이 쌓이기 때문에 데이터 저장소의 크기 관리가 중요한 관리 요건이 된다.

월 혹은 년 기준으로 파티션 테이블을 만들어서, 특정 기간 이후의 이력 데이터는 제거(purge)하는 식으로 사용한다면 데이터가 비대해지는 것은 예방할 수 있지 않을까 싶다.