고객사 기술지원을 하다보면, 데이터 추출/적재 작업은 개발 프로그램을 통하여 진행하고자 하는 요건이 발생한다.
개발자들이 이관 작업을 수행하는 경우는 개발 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 경로를 설정하여 주도록 한다.