[이관] admin_cmd 프로시저를 이용한 이관 자동화

고객사 기술지원을 하다보면, 데이터 추출/적재 작업은 개발 프로그램을 통하여 진행하고자 하는 요건이 발생한다.

개발자들이 이관 작업을 수행하는 경우는 개발 language를 통해서 구현하고 싶어하고, DBA가 수행하는 경우는 shell 방식으로 처리하는 경우가 보편적이지 않을까 싶다.

 

admin_cmd를 이용하여 다양한 관리 작업을 개발 레벨에서 관리를 할 수 있다. 자세한 정보는 정보센터를 통해서 확인이 가능하다.

문서: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0012547.html

 

0. 테스트 환경

- DB2: V10.5

- 오라클 호환성: 적용하지 않음

- 인스턴스명: db2inst

- DB 명: sample

- 테이블: employee

 

1. 데이터 추출/적재(export/load)

- procedure code

  문법 체크 및 debug를 위해서 dbms_output.put_line() 를 사용하였고, export/load의 실행 로그 확인을 위해 커서를 선언하여 출력 변수로 추출하여 반환하도록 구현하였다.

db2 +p -td@ << EOF connect to sample @ create or replace procedure sp_load ( IN I_VAR1 char(3), OUT O_EXP_MSG varchar(256), OUT O_LOA_MSG varchar(256), OUT O_MSG varchar(256)) specific sp_load dynamic result sets 2 begin      DECLARE EXP_ROWS_EXPORTED  BIGINT ;      DECLARE EXP_RETRIEVAL      VARCHAR(512);      DECLARE EXP_REMOVAL        VARCHAR(512) ;           DECLARE LOA_ROWS_READ      BIGINT ;      DECLARE LOA_ROWS_SKIPPED   BIGINT ;      DECLARE LOA_ROWS_LOADED    BIGINT ;      DECLARE LOA_ROWS_REJECTED  BIGINT ;      DECLARE LOA_ROWs_DELETED   BIGINT ;      DECLARE LOA_ROWS_COMMITED  BIGINT ;      DECLARE LOA_ROWS_PARTITIONED BIGINT ;      DECLARE LOA_NUM_AGENTINFO_ENTRIES BIGINT;      DECLARE LOA_MSG_RETRIEVAL  VARCHAR(512) ;      DECLARE LOA_MSG_REMOVAL    VARCHAR(512) ;           DECLARE P_STMT             VARCHAR(2048) ;      DECLARE P_EXPORT_PATH      VARCHAR(512) ;      DECLARE P_EXPORT_FILE      VARCHAR(32)  ;           DECLARE EXP_RESULT             RESULT_SET_LOCATOR VARYING;      DECLARE LOA_RESULT             RESULT_SET_LOCATOR VARYING;           declare EXIT handler for SQLEXCEPTION      begin            get DIAGNOSTICS EXCEPTION 1 O_MSG = MESSAGE_TEXT;      end ;           SET P_EXPORT_PATH = '/db2user/db2inst/script/' ;      SET P_EXPORT_FILE = 'emp.ixf' ;      SET P_STMT = 'select * from emp where workdept = ''' || I_VAR1 || '''' ;           call dbms_output.put_line('export to ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server ' || P_STMT) ;           call sysproc.admin_cmd('export to ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server ' || P_STMT) ;           associate result set locators(EXP_RESULT) with procedure sysproc.admin_cmd ;      allocate EXP_CUR cursor for result set EXP_RESULT ;      fetch from EXP_CUR into EXP_ROWS_EXPORTED, EXP_RETRIEVAL, EXP_REMOVAL ;           call dbms_output.put_line('ROWS_EXPORTED : ' || TO_CHAR(EXP_ROWS_EXPORTED) || ',  RETRIVAL : ' || EXP_RETRIEVAL) ;      SET O_EXP_MSG = EXP_RETRIEVAL ;                  call dbms_output.put_line('load from ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;           call sysproc.admin_cmd('load from ''' || P_EXPORT_PATH || P_EXPORT_FILE || ''' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;      associate result set locators(LOA_RESULT) with procedure sysproc.admin_cmd ;      allocate LOA_CUR cursor for result set LOA_RESULT ;      fetch from LOA_CUR into LOA_ROWS_READ, LOA_ROWS_SKIPPED, LOA_ROWS_LOADED, LOA_ROWS_REJECTED, LOA_ROWS_DELETED, LOA_ROWS_COMMITED, LOA_ROWS_PARTITIONED, LOA_NUM_AGENTINFO_ENTRIES, LOA_MSG_RETRIEVAL, LOA_MSG_REMOVAL ;           call dbms_output.put_line('ROWS_READ(' ||TO_CHAR(LOA_ROWS_READ) || ') ROWS_SKIPPED(' || TO_CHAR(LOA_ROWS_SKIPPED) || ') ROWS_LOADED(' || TO_CHAR(LOA_ROWS_LOADED) || ') ROWS_REJECTED(' || TO_CHAR(LOA_ROWS_REJECTED) || ') ROWS_DELETED('|| TO_CHAR(LOA_ROWS_DELETED) || ') ROWS_COMMITED(' || TO_CHAR(LOA_ROWS_COMMITED) || ') MSG_RETRIEVAL(' || LOA_MSG_RETRIEVAL) ;      SET O_LOA_MSG = LOA_MSG_RETRIEVAL ;      --select sqlcode, msg into O_LOA_SQLCODE,O_LOA_MSG from table (sysproc.admin_get_msgs(LOA_MSG_RETRIEVAL)) as msg ;      --call sysproc.admin_remove_msgs(LOA_MSG_REMOVAL) ;      end @ terminate @ EOF

 

실행 테스트

Database Connection Information Database server        = DB2/AIX64 10.5.2 SQL authorization ID   = DB2INST Local database alias   = SAMPLE [db2inst@dbcni1:/db2user/db2inst/script] $ db2 set serveroutput on DB20000I  The SET SERVEROUTPUT command completed successfully. [db2inst@dbcni1:/db2user/db2inst/script] $ db2 "call sp_load ('A00', ?,  ?, ?)"   Value of output parameters   --------------------------   Parameter Name  : O_EXP_MSG   Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('15911_503_DB2INST')) AS MSG   Parameter Name  : O_LOA_MSG   Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('17722_11123_DB2INST')) AS MSG   Parameter Name  : O_MSG   Parameter Value : -   Return Status = 0 export to '/db2user/db2inst/script/emp.ixf' of ixf messages on server select * from emp where workdept = 'A00' ROWS_EXPORTED : 5,  RETRIVAL : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('2786_6890_DB2INST')) AS MSG load from '/db2user/db2inst/script/emp.ixf' of ixf messages on server insert into t_emp nonrecoverable indexing mode autoselect ROWS_READ(5) ROWS_SKIPPED(0) ROWS_LOADED(5) ROWS_REJECTED(0) ROWS_DELETED(0) ROWS_COMMITED(5) MSG_RETRIEVAL(SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28553_15499_DB2INST')) AS MSG

 

 

2. 데이터 적재(load cursor 방식)

export하지 않고 cursor에 담아 load 처리하는 방법

 

db2 +p -td@ << EOF connect to sample @ create or replace procedure sp_cur ( IN I_VAR1 char(3), OUT O_LOA_MSG varchar(256), OUT O_MSG varchar(256)) specific sp_cur dynamic result sets 1 begin      DECLARE LOA_ROWS_READ      BIGINT ;      DECLARE LOA_ROWS_SKIPPED   BIGINT ;      DECLARE LOA_ROWS_LOADED    BIGINT ;      DECLARE LOA_ROWS_REJECTED  BIGINT ;      DECLARE LOA_ROWs_DELETED   BIGINT ;      DECLARE LOA_ROWS_COMMITED  BIGINT ;      DECLARE LOA_ROWS_PARTITIONED BIGINT ;      DECLARE LOA_NUM_AGENTINFO_ENTRIES BIGINT;      DECLARE LOA_MSG_RETRIEVAL  VARCHAR(512) ;      DECLARE LOA_MSG_REMOVAL    VARCHAR(512) ;           DECLARE P_STMT             VARCHAR(2048) ;      DECLARE LOA_RESULT             RESULT_SET_LOCATOR VARYING;           declare EXIT handler for SQLEXCEPTION      begin            get DIAGNOSTICS EXCEPTION 1 O_MSG = MESSAGE_TEXT;      end ;           SET P_STMT = 'select * from emp where workdept = ''' || I_VAR1 || '''' ;                call dbms_output.put_line('load from ( database sample ' || P_STMT || ' ) of cursor messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;           call sysproc.admin_cmd('load from ( database sample ' || P_STMT || ' ) of cursor messages on server insert into t_emp nonrecoverable indexing mode autoselect') ;      associate result set locators(LOA_RESULT) with procedure sysproc.admin_cmd ;      allocate LOA_CUR cursor for result set LOA_RESULT ;      fetch from LOA_CUR into LOA_ROWS_READ, LOA_ROWS_SKIPPED, LOA_ROWS_LOADED, LOA_ROWS_REJECTED, LOA_ROWS_DELETED, LOA_ROWS_COMMITED, LOA_ROWS_PARTITIONED, LOA_NUM_AGENTINFO_ENTRIES, LOA_MSG_RETRIEVAL, LOA_MSG_REMOVAL ;           call dbms_output.put_line('ROWS_READ(' ||TO_CHAR(LOA_ROWS_READ) || ') ROWS_SKIPPED(' || TO_CHAR(LOA_ROWS_SKIPPED) || ') ROWS_LOADED(' || TO_CHAR(LOA_ROWS_LOADED) || ') ROWS_REJECTED(' || TO_CHAR(LOA_ROWS_REJECTED) || ') ROWS_DELETED('|| TO_CHAR(LOA_ROWS_DELETED) || ') ROWS_COMMITED(' || TO_CHAR(LOA_ROWS_COMMITED) || ') MSG_RETRIEVAL(' || LOA_MSG_RETRIEVAL) ;      SET O_LOA_MSG = LOA_MSG_RETRIEVAL ; end @ terminate @ EOF

 

실행 테스트

$ db2 connect to sample user db2inst using $ db2 set serveroutput on $ db2 "call sp_cur('A00',?,?)"   Value of output parameters   --------------------------   Parameter Name  : O_LOA_MSG   Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('22620_26387_DB2INST')) AS MSG   Parameter Name  : O_MSG   Parameter Value : -   Return Status = 0 load from ( database sample select * from emp where workdept = 'A00' ) of cursor messages on server insert into t_emp indexing mode autoselect ROWS_READ(5) ROWS_SKIPPED(0) ROWS_LOADED(5) ROWS_REJECTED(0) ROWS_DELETED(0) ROWS_COMMITED(5) MSG_RETRIEVAL(SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('22620_26387_DB2INST')) AS MSG

 

참고로 lob 컬럼이 있는 경우는 export 및 import/load 시 추가 옵션이 필요하며, 대용량 데이터의 load 작업 경우 temp 경로를 설정하여 주도록 한다.