문법 체크 및 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 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('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 ;
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 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 ;
$ 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 경로를 설정하여 주도록 한다.