데이터 이관 작업 시 lob 데이터를 고려하지 않고 export 나 load를 수행하게 된다.

많은 테이블에 대해서 이관 작업을 급히 수행해야 되는 경우, 이관 대상의 테이블 컬럼 정보를 확인하지 않고 수행을 하다 보면 lob 데이터가 있는 테이블은 load 시 “문자 짤림" 로그가 남게 된다.

 

데이터 코드 셋이 잘못되어서 발생하는 경우도 있지만, lob 컬럼이 속한 테이블 경우 추출/적재 시 lob 컬럼에 대한 옵션을 주지 않으면 발생하기도 한다. 또한 load 작업 시 여러 이유에서 실패를 하거나 중단되는 경우 load 작업을 위한 임시 공간이 full 발생하여 load 작업이 실패하게 되는 경우도 발생한다.

 

데이터 이관 작업 시, lob 데이터 및 대용량 데이터 이관을 고려하여 다음과 같이 export 및 import/load 구문을 사용하는 것이 바람직하지 않을까 싶다.

 

1. export 구문

  lob 컬럼이 존재하는 테이블을 LOBS TO 옵션을 주어 별도의 경로에 데이터를 저장하도록 함

  (녹색 글자: 경로 및 파일명을 의미함, {}: shell 변수들을 의미함)

export to export/${TABSCHEMA}.${TABNAME}.ixf of ixf LOBS TO lobs/${TABNAME}  modified by lobsinfile messages export/${TABSCHEMA}.${TABNAME}.exp select * from ${TABSCHEMA}.${TABNAME} with ur

 

2. load 구문

  LOB 데이터의 경로를 LOBS FROM 옵션을 통하여 지정하고, load 작업 시 필요한 임시 공간은 tempfiles path 옵션으로 지정함

load from export/${TABSCHEMA}.${TABNAME}.ixf of ixf LOBS FROM lobs/${TABNAME}  modified by lobsinfile messages ${TABSCHEMA}.${TABNAME}.msg tempfiles path tmp replace into ${TABSCHEMA}.${TABNAME}

'Note' 카테고리의 다른 글

[성능] Actual Section & db2caem  (0) 2014.02.28
[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17

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

개발자들이 이관 작업을 수행하는 경우는 개발 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 경로를 설정하여 주도록 한다.

'Note' 카테고리의 다른 글

[성능] SQL Optimizer Profile  (0) 2014.02.06
[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[SQL] 오라클 호환성 VARCHAR2에 대한 고찰  (0) 2014.01.07
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28

오라클 호환성 기능은 DB2_COMPATIBILITY_VECTOR 레지스트리 변수에 값을 적용함으로서 오라클 문법/표현등을 사용하게 된다.

그러나 일부 기능은 데이터베이스를 생성하기 전에만 적용되는 것들이 있다.

Developerworks에 나온 문서(https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/about_the_db2_compatibility_vector_and_what_not_to_say_at_the_dinner_table282?lang=en) 의 내용 및 기억을 더듬어보면

1. NUMBER

2. VARCHAR2

3. DATE

4. Oracle Data Dictionary

정도가 아닌가 싶다.

 

이 중 VARCHAR2를 세부적으로 들여다보면 특별한 기능을 포함하고 있다.

오라클이 공백과 NULL 값 구분을 하지 않기 때문에, DB2로 Data Migration을 하게 되면 이것은 문제가 된다. (DB2는 공백과 NULL을 구분하기에)

 

따라서 SQL 내에서 공백의 데이터를 처리하다보면 Oracle과는 다른 결과를 갖게 된다.

이 문제를 해결하기 위해서 VARCHAR2 (0x20) 값을 적용할 필요가 생긴다.

 

1. DB2에서 공백 값 테스트

$ db2 "values (COALESCE('','NULL'))"

1
----


$ db2 "values (COALESCE(' ','NULL'))"

1
----


$ db2 "values (COALESCE(NULL,'NULL'))"

1
----
NULL



$> db2 "values (bigint(''))"

1
--------------------


SQL0420N  Invalid character found in a character string argument of the function "BIGINT".  SQLSTATE=22018

 

2. 오라클의 공백 값 테스트

SQL> select nvl('','NULL') as C1 from dual;

C1
----
NULL


SQL> select nvl(' ','NULL') as C2 from dual;

C
-


SQL> select nvl(NULL,'NULL') as C3 from dual;

C3
----
NULL



SQL> select to_number('') as C4 from dual;

        C4
----------

 

4번째 SQL을 통하여 DB2에서는 “공백 값"이 숫자로 캐스팅되지 못하고 SQL0420N 오류가 발생했다.

 

오라클 호환성 (db2set DB2_COMPATIBILITY_VECTOR=20) 을 적용해도 “공백"값은 숫자형으로 형 변환되지 못한다.

 

이 경우 오라클처럼 “공백" 값을  숫자로 형변환 되도록 하기 위해서는 db2set DB2_COMPATIBILITY_VECTOR=20 를 적용하고 DB 재생성을 해야 된다.

$> db2set -all
[i] DB2_COMPATIBILITY_VECTOR=20

$> db2 drop db sample
$> db2stop
$> db2start
$> db2sampl
$> db2 connect to sample
$> db2 "values (bigint(''))"

1
--------------------
                   -

 

위 문제는 사전 테스트를 통하여 “공백"처리 문제가 발생함을 확인하는 경우 VARCHAR2 호환성을 적용되어야 할 것이다.

그렇지 않다면 SQL 수정이 되어야 할 것이다.

 

다른 관점에서는 “해당 DB가 오라클 호환성을 적용하고 생성된 DB인지” 확인하는 방법이 될 수도 있다.

예를 들어 DB2 구성 작업에 많은 수의 인스턴스 및 DB 생성 작업 시,  “오라클 호환성"을 적용하고 DB가 만들어져야 되는 상황이라면

오라클 호환성이 제대로 적용되어 생성된 것인지 확인하고 싶을 수 있다.

- 많은 반복 작업으로 작업 검증 절차를 빠뜨리거나

- 인스턴스 구성 후 자동으로 인스턴스가 실행 중인 상황에서, 호환성 적용하고 (인스턴스 재시작 없이) DB를 생성된 것은 아닌지

- 이미 구축된 DB2 환경에 (히스토리 없이 지원을 하게 되어) 오라클 호환성 관련된 문제가 발생한 경우 호환성 적용 시점이 언제인지 확인할 때

'Note' 카테고리의 다른 글

[이관] lobsinfile 및 tempfiles path  (0) 2014.01.27
[이관] admin_cmd 프로시저를 이용한 이관 자동화  (0) 2014.01.27
[개발] db2dclgn (선언부 생성기)  (0) 2013.12.17
[개발] db2jcc 와 db2jcc4 의 차이  (0) 2013.11.28
[개발] Windows에서 ESQL 컴파일 및 Connect By Siblings  (0) 2013.11.28

+ Recent posts

티스토리툴바