[MIGRATION]소스 컬럼과 타겟 컬럼 매핑

m_job_table : 마이그레이션 대상 소스 소유자, 소스 테이블, 타겟소유자, 타겟 테이블 정보

DBA_TAB_COLUMNS : 오라클 데이터베이스의 테이블 컬럼 정보,  타겟 오라클에서 관리

 

m_job_table 에 소스 대상 컬럼과 타겟 대상 컬럼을 컬럼 형태로 저장하고

마이그레이션 쉘에서 이 정보를 받아와서 소스 EXTERNAL TABLE 생성, 타겟 EXTERNAL TABLE 생성 테이블, INSERT 시에 활용

 

m_job_table 구조

CREATE TABLE MIGADMIN.M_JOB_TABLE (     WORK_AREA_CD    VARCHAR2(50),     WORK_DIV        VARCHAR2(1),     SHL             VARCHAR2(255),     WORKFLOW_SEQ    NUMBER,     WORKFLOW_NAME   VARCHAR2(255),     WORK_AREA_NAME  VARCHAR2(255),     SRC_OWNER       VARCHAR2(20),     SRC_TBL         VARCHAR2(255),     SRC_TBL_COMTS   VARCHAR2(255),     SRC_RECORD_CNT  NUMBER,     SRC_TBL_SIZE    NUMBER,     SRC_COLS        VARCHAR2(4000), <-- 앞으로 설명할 소스 컬럼     TGT_OWNER       VARCHAR2(20),     TGT_TBL         VARCHAR2(255),     TGT_COLS        VARCHAR2(4000), <-- 앞으로 설명할 타겟 컬럼     ENCRYPT_YN      VARCHAR2(1),     MAPPING_TYPE    VARCHAR2(1),     TBL_FILTER      CHAR(1),     UPDATE_DT       DATE )

 

m_job_column 테이블은 컬럼 별 정보를 담고 있는 테이블 

m_job_column 구조

CREATE TABLE MIGADMIN.M_JOB_COLUMN (     WORK_AREA_CD    VARCHAR2(50),     SHL             VARCHAR2(255),     TGT_OWNER       VARCHAR2(20),     TGT_TBL         VARCHAR2(255),     TGT_COL         VARCHAR2(255),     TGT_COL_NM      VARCHAR2(1000),     TGT_COL_SEQ     NUMBER(4),     TGT_COL_TYPE    VARCHAR2(255),     TGT_COL_LEN     NUMBER(4),     TGT_COL_NULL    VARCHAR2(1),     TGT_ENCRYPT_YN  VARCHAR2(1),     TGT_ENCRYPT_KEY VARCHAR2(255),     ETL_LOGIC       VARCHAR2(255),     SRC_OWNER       VARCHAR2(20),     SRC_TBL         VARCHAR2(255),     SRC_COL         VARCHAR2(255),     SRC_COL_NM      VARCHAR2(1000),     SRC_COL_SEQ     NUMBER(4),     SRC_COL_TYPE    VARCHAR2(255),     SRC_COL_LEN     NUMBER(4),     SRC_COL_NULL    VARCHAR2(1),     SRC_ENCRYPT_YN  VARCHAR2(1),     DEL_YN          CHAR(1) DEFAULT 'N',     UPDATE_DT       DATE DEFAULT sysdate )

 

아래는 소스 테이블 컬럼과 타겟 테이블 컬럼간의 1:1 매핑, 추가, 삭제된 컬럼을 조사하기 위한 SQL 임

1

소스

2

select * from  (select   a.src_owner as src_src_owner , a.src_tbl as src_src_tbl , a.tgt_owner as src_tgt_owner , a.tgt_tbl as src_tgt_tbl , b.column_name as src_col_nm , b.column_id as src_col_id , b.data_type as src_data_type , b.data_length as src_data_length , b.data_precision as src_data_precision , b.data_scale as src_data_scle , b.nullable as src_nullable  from m_job_table a, dba_tab_columns@소스링크 b  where a.src_owner = b.owner  and   a.src_tbl   = b.table_name  and   a.work_div = 'H' ) a full outer join  (select   a.src_owner as tgt_src_owner , a.src_tbl as tgt_src_tbl , a.tgt_owner as tgt_tgt_owner , a.tgt_tbl as tgt_tgt_tbl , b.column_name as tgt_col_nm , b.column_id as tgt_col_id , b.data_type as tgt_data_type , b.data_length as tgt_data_length , b.data_precision as tgt_data_precision , b.data_scale as tgt_data_scle , b.nullable as tgt_nullable  from m_job_table a, dba_tab_columns b  where a.tgt_owner = b.owner  and   a.tgt_tbl   = b.table_name  and   a.work_div = 'H' ) b on (a.src_src_owner = b.tgt_src_owner and a.src_src_tbl = b.tgt_src_tbl and a.src_tgt_owner = b.tgt_tgt_owner and a.src_tgt_tbl = b.tgt_tgt_tbl and a.src_col_nm = b.tgt_col_nm ) order by a.src_src_owner, a.src_src_tbl, a.src_col_id ;

 

위 테이블을 이용하여 소스 테이블에서 추가/삭제된 컬럼, 타겟 테이블에서 추가/삭제된 컬럼을 조사하여 이에 맞는 컬럼을 명시 쉘에서 이용한다.

 

예제에 사용할 테이블  

소스 테이블 T1	타겟 테이블 T2	타겟 테이블 T3
create table t1 ( a not null b, c )	create table t2 ( a1 not null b1, c1 , d1 not null )	create table t3 ( a1 not null, b1 not null )

소스 테이블 T1에서 타겟 테이블 T2로 마이그레이션

a는 a1으로 컬럼 변경, b--> b1, c -> c1

T2의 d 컬럼은 T1에 없는 컬럼이지만 not null 속성으로 업무에서 선정하는 디폴트 값을 넣어주어야 에러가 발생하지 않는다.

 

소스 테이블 T1에서 타겟 테이블 T3 로 마이그레이션

a는 a1으로 컬럼 변경, b--> b1

T1의 c 컬럼은 T2에 없는 컬럼이므로 소스 external table 생성시 부터 select-list에서 제거하여 처리함

T1의 b컬럼은 b1으로 컬럼명이 변경되면서 not null 속성으로 바뀌었으므로 T1의 b 컬럼이 null 인 경우 디폴토로 넣어 줄 선정값을 정한다.

 

[case 1]

-- T2 기준으로 소스 컬럼을 만든다.

-- T1에 없는 T2 속성 중 NULL 속성인 것은 제외

v_src_cols	a as a1, b as b1, c as  c1, NVL(d, 'default') as d1
v_tgt_cols	a1, b1, c1, d1

 

[case 2]

-- T2 기준으로 소스 컬럼을 만든다.

-- T1에 없는 T2 속성 중 NULL 속성인 것은 제외

v_src_cols	a as a1, NVL(b, 'default') as b1
v_tgt_cols	a1, b1

 

단계는 이렇다

소스 테이블 --> 소스 external 테이블 --> ftp 전송 --> 타겟 external 테이블 --> 타겟 테이블

 여기서 ftp 전송은 오라클에서 제공하는 EXEC DBMS_FILE_TRANSFER.GET_FILE 함수를 사용하여 소스에서 external 테이블과 같이 생성된 dmp 파일을 타겟으로 가져온다.

 

case	소스 external table 추출	타겟 external 추출	탸겟 Insert
case 1	CREATE TABLE ext_${v_rep_tbl} ORGANIZATION EXTERNAL AS SELECT ${v_src_cols} FROM ${v_src_owner}.${v_src_tbl)  a	CREATE TABLE ext_${v_rep_tbl} ORGANIZATION EXTERNAL AS SELECT ${v_tgt_cols}   FROM ${v_tgt_owner}.${v_tgt_tbl} a  WHERE 0 = 1;	INSERT /*+ APPEND PARALLEL(A 2) */ INTO ${v_tgt_owner}.${v_tgt_tbl} (${v_tgt_cols}) SELECT /*+ PARALLEL(A 2) */ (${v_tgt_cols}) FROM ext_${v_rep_tbl} a;
case 2	CREATE TABLE ext_${v_rep_tbl} ORGANIZATION EXTERNAL AS SELECT ${v_src_cols} FROM ${v_src_owner}.${v_src_tbl)  a	CREATE TABLE ext_${v_rep_tbl} ORGANIZATION EXTERNAL AS SELECT ${v_tgt_cols1}   FROM ${v_tgt_owner}.${v_tgt_tbl} a  WHERE 0 = 1;	INSERT /*+ APPEND PARALLEL(A 2) */ INTO ${v_tgt_owner}.${v_tgt_tbl} (${v_tgt_cols}) SELECT /*+ PARALLEL(A 2) */ (${v_tgt_cols}) FROM ext_${v_rep_tbl} a;