안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번에는 총 3회에 걸쳐서 ORACLE과 DB2 UDB의 데이터 이동에 대해서 살펴보도록 하겠습니다.
크게 세 가지로 나누어서 다룰 예정인데, 첫째는 ORACLE의 데이터 이동 방법에 대해서이고,
둘째는 DB2 UDB의 데이터 이동 방법이 될 것입니다.
그리고 마지막으로 이 두 DB의 데이터 이동 TOOL에 대해서 비교분석 해 보겠습니다.
이번에는 ORACLE의 데이터 이동 방법(IMP, SQL Loader)에 대해 살펴보도록 하겠습니다.
DB2 UDB와 Oracle의 데이터 이동
Introduction
데이터베이스 사이의 데이터 이동은 DBA의 일상 업무 중 한가지 입니다.
여기서는 DB2와 Oracle 에서의 데이터 이동에 대한 툴과 명령어를 살펴보고, 두 DBMS를
비교해 보겠습니다.
Topics
여기서는 두 DBMS 가 모두 지원하는 데이터 이동 유틸리티와 그 장단점을 다음의 주제를
통해 살펴보겠습니다.
- Import
- Tool 비교
- Export
- 기타 툴
- 데이터 이동 시나리오
Data movement Requirements
일반적으로 DBA는 데이터를 운영환경의 더 큰 파일시스템으로 옮긴다든지, 테스트를 위해
운영데이터의 일부를 생성한다든지, 개발을 위해 전체 데이터를 복제하는 등의 작업을
수행하게 됩니다. 개발환경에서 개발자들은 복제된 데이터를 가지고 테스트를 수행하고,
해당 데이터를 임의로 삭제할 수 있습니다. 그리고 데이터가 다시 필요하게 되면 간단한
복구를 통해 다른 테스트를 위해 원래 상태로 되돌릴 수 있습니다.
하지만 웨어하우스 환경처럼 테라바이트급의 정보를 가진 곳이라면, 테스트를 위해 전체
데이터베이스를 복제한다는 것은 거의 불가능 한 일입니다. 이런 경우, RI가 그대로
유지된 데이터의 일부가 필요할 것입니다. 그리고 개발자 들을 위해 테스트 환경에 여러
데이터 세트를 배치시킬 필요도 있을 것입니다. 또한 테스트가 끝나면 운영환경으로
데이터를 옮겨야 하는 경우도 있고 데이터베이스를 새로운 플랫폼으로 이주 시켜야 할
경우도 있을 것입니다.
종합해 보면, 대부분의 데이터 이동 시나리오는 다음과 같습니다.
- 현재 파일시스템이 가득 찼을 경우, 데이터 파일을 새로운 파일시스템으로 옮기는 작업
- 테스트나 개발을 위해 전체 혹은 일부 데이터를 옮기는 작업
- 데이터베이스를 다른 플랫폼으로 이주시키는 작업
이런 경우 DBA는 데이터베이스의 크기나 관리 윈도우, 그리고 네트워크 I/O, CPU,
메모리 등 시스템 자원을 고려해서 데이터 이동 방법을 선택할 것입니다.
이제 어떤 데이터 이동 방법들이 있는지 살펴 보겠습니다.
Import Utilities
데이터 import를 위해 Oracle이 제공하는 유틸리티를 살펴보겠습니다.
Oracle에서는 데이터를 import 하는데 다음의 유틸리티를 제공합니다.
- Oracle - imp
- Oracle - SQL Loader
Oracle - imp
오라클의 import 유틸리티인 imp는 100MB에서 10GB 정도의 중,소 데이터를 import 하는데
사용됩니다. import 작업을 빨리 하기 위해, 여러 개의 imp 작업을 동시에 수행시킬 수
있습니다. imp를 사용하기 위해서는, /rdbms 디렉토리에 있는 catalog.sql 스크립트나
catexp.sql 스크립트를 우선 수행시켜야 합니다.
imp 를 수행하는데 있어서 CONNECT 롤에 포함된 CREATE SESSION 권한이 필요합니다.
import 에는 두 가지 import 스키마 모드가 있습니다.
- 동일 사용자 스키마로 Import :
같은 사용자 스키마에 오브젝트를 import 하려면, 사용자는 RESOURCE role 같이 시스템 권한과
오브젝트 권한을 모두 가지고 있어야 합니다.
- 다른 사용자 스키마로 Import :
데이터베이스 오브젝트는 한 사용자로 export 하고 다른 사용자로 import 할 수 있습니다.
만약 다른 스키마로 import 하는 경우, IMP_FULL_DATABASE role 이 필요합니다.
import 문법은 다음과 같습니다. 전체 설명을 보려면 명령창에서 imp help=y 명령을 수행하십시오.
Oracle imp의 Syntax
|
imp keyword=value [,keyword=value,...]
Keyworkds :
USERID username/password BUFFER size of data buffer
FILE input files (EXPDAT.DMP) SHOW list file contents (N)
IGNORE ignore create errors (N) GRANTS import grants (Y)
INDEXES import indexes (Y) ROWS import data rows (Y)
LOG log file of screen output FULL import entire file (N)
FROMUSER list of owner usernames TOUSER list of usernames
TABLES list of table names RECORDLENGTH length of IO record
INCTYPE incremental import type COMMIT commit array insert (N)
PARFILE parameter filename CONSTRAINTS import constraints (Y)
|
imp 유틸리티를 수행하는 방법은 세가지가 있습니다.
- 명령창에서
예) imp system/password fromuser=scott touser=scott file=acct_pay.dmp
그림.1 명령창에서 Oracle imp 유틸리티 수행
- parfile 옵션을 통해 명령창에서
예) imp system/manager parfile=Acct_Pay.txt
그림.2 명령창에서 parfile 옵션을 사용하여 Oracle imp 유틸리티 수행
- 명령창에서 대화식으로
그림.3 명령창에서 대화식으로 Oracle import 유틸리티 수행
import 에는 4가지 모드가 있습니다. 이들 중 import 유틸리티 사용 예를 통해, 몇 가지
고려사항을 살펴보겠습니다. 이를테면 에러를 최소화 하려면 CONSTRAINT=N 을 사용해서
RI를 해제하는 것이 좋습니다. imprort 가 끝난 뒤에는 다시 constraint 가 적용됩니다.
Trigger 도 역시 해제해 주어야 합니다. 일반적으로 import 작업이 끝난 뒤에 trigger를
생성하는 스크립트를 돌리는 방법을 사용합니다.
Import는 Oracle exp 유틸리티를 통해 생성된 export dump 만이 사용됩니다.
상위버전(최신)의 imp 는 동일버전 및 하위버전(이전)의 export dump를 읽을 수 있지만,
하위버전의 imp가 상위버전의 export dump를 읽을 경우에는 에러가 발생합니다.
Import 의 4가지 모드의 예를 살펴 보겠습니다.
- Full Import : FULL=Y
FULL=Y 파라미터를 사용하면 기존에 FULL=Y 파라미터로 export 된 모든 오브젝트가
import 됩니다. 전체 import 가 수행될 수 있는지 확인하기 위한 단계가 있음에
유의하십시오.
- 사용자 레벨 import : FromUser 와 Touser
예를 들어, scott 사용자 스키마에서 모든 오브젝트를 import 하려면 다음 명령을
수행하십시오.
imp system/password fromuser=scott touser=allan file=Acct_Pay.dmp
그림.4 Fromuser 와 Touser 파라미터를 사용한 import
- 테이블 단위 import : Tables=(*)
- Tables=(*)를 사용한 import.
스키마가 scott인 모든 테이블을 import 하려면 다음 명령을 수행하십시오
imp system/password fromuser=scott touser=allan file=Acct_Pay.dmp tables=(*)
그림.5 모든 테이블을 import
- Tables=(A,B,C) 를 이용한 import.
예) imp system/password fromuser=scott touser=allan file=Acct_Pay.dmp tables=(BONUS, EMP) constraint=N
그림.6 일부 테이블만 import
- 파티션된 테이블 Import : Tables=(T:p1, T:p2)
예) imp scott/tiger file=Scott.dmp tables=(empp:p1, empp:p2)
그림.7 파티션된 테이블 import
- 테이블스페이스 레벨의 import
Transport_Tablespace=Y 와 Tablespaces(A,B,C) 와 Datafiles=xxx
Oracle - SQL Loader
SQL Loader는 외부 파일을 Oracle 데이터베이스에 로드하기 위해 제공되는 유틸리티입니다.
SQL Loader는 imp보다 강력하고 유연한 것으로 보입니다. SQL Loader는 로드될 데이터를
걸러내고, 동시에 로드될 데이터를 수정하는 Oracle SQL 함수를 적용시킬 수 있습니다.
가변/고정 문자열과 stream 데이터 외에도 객체지향 데이터, LOB 데이터, collection 등을
로드 할 수 있습니다.
로드에는 conventional path와 direct path의 2가지 모드가 있습니다.
Oracle 9i부터는 (parallelism과 direct insert를 사용한) 외부테이블을 사용하는 것이
가장 빠른 방법입니다. 외부테이블을 사용하면, 파일의 내용을 로드하기 위해 SELECT 문을
사용할 수 있습니다.
Oracle SQL Loader의 문법은 다음과 같고, 자세한 문법은 명령창에서 sqlldr 명령을
수행하면 볼 수 있습니다.
Syntax for SQL Loader
|
SQLLDR keyword=value [,keyword=value,...]
Keywords
userid -- ORACLE username/password control -- Control file name
log -- Log file name bad -- Bad file name
data -- Data file name discard -- Discard file name
discardmax -- Number of discards to allow (Default all)
skip -- Number of logical records to skip (Default 0)
load -- Number of logical records to load (Default all)
errors -- Number of errors to allow (Default 50)
|
SQL Loader를 실행하는 방법은 명령창을 통해서만 가능합니다. SQL Loader를 사용하기
위해서는 컨트롤 파일이 필요합니다. 컨트롤 파일은 파일의 위치, 포맷, insert target 등의
정보를 제공하는 SQL Loader의 두뇌와 같은 역할을 합니다. 컨트롤 파일은 대소문자를
구분하며 특정 파라미터가 정의된 형식이 없는 텍스트 파일입니다.
몇 가지 SQL Loader 의 사용 예를 살펴 보겠습니다.
- 고정 길이 레코드를 load
sqlldr userid=scott/tiger log=course.log, control=course.ctl
고정길이 데이터의 예, course.dat
|
CS3121Theory of Computation I F Vari Hall*****
CS3122Theory of Computation II W Norman Bethune
CS4101Computer Robotics ****** W Stong College*
CS4102Computer Graphics ****** W Earth Science*
CS4120Advanced Relational **** F Winter College
|
course.ctl
|
LOAD DATA
INFILE 'course.dat' "fix 49"
BADFILE 'course.bad'
Insert
INTO TABLE Course
(courseid position (1:6) char, coursetitle position (7:30) char,
term position (32:32) char, location position (34:47) char)
|
- 가변 길이 레코드를 load
sqlldr scott/tiger control=emp.ctl, log=emp.log
가변길이 데이터의 예. emp.dat
|
1234, "Allan", "IT Specialist", 2345, "April-15-2002", 15000, 3000, 88
2345, Lily Ng, "HR", 7766, Jan-12-2000, 9000, 2000, 55
3456, "Odelia", "Fun", 8899, "June-11-2001", 8000,1000, 77
4567, "Titus", Fun, 6655,"Aug-03-2005", 5000,200, 76
5678, "Timothy", Sales, 7788, Aug-99-2005", 4000, 2000, 11
|
emp.ctl
|
LOAD DATA
INFILE 'emp.dat' -- emp.dat is the input file
BADFILE 'emp.bad' -- bad file
DISCARDFILE 'emp.dsc' -- discard file
DISCARDMAX 2 -- Max discards allowed before load terminates
APPEND
INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- terminated by comma. Allow double quote
(empno, ename, job, mgr, hiredate date(30) "Month-DD-YYYY", sal, comm, deptno)
|
- 여러 테이블에 load
sqlldr scott/tiger control=mul.ctl, log=multitables.log
가변길이 데이터의 예. emporig.dat
|
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
|
mul.ctl
|
LOAD DATA
INFILE emporig.dat
BADFILE multables.bad
DISCARDFILE multables.dsc
APPEND INTO TABLE emp1 when empno<>'7788'
FIELDS TERMINATED BY X'2c' OPTIONALLY ENCLOSED BY '"' TRAILING
NULLCOLS
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(7:12) INTEGER EXTERNAL)
INTO TABLE emp2 when job = 'CLERK'
FIELDS TERMINATED BY X'2c' OPTIONALLY ENCLOSED BY '"' TRAILING
NULLCOLS
(empno POSITION(1:4) INTEGER EXTERNAL,
job POSITION(14:22) INTEGER EXTERNAL)
|
- 파티션된 데이터에 load
sqlldr scott/tiger control=partition.ctl log=partition.log
샘플 데이터 emp_orig.dat
|
7369, SMITH,CLERK,7902,17-DEC-80,800,20
7499, ALLEN,SALESMAN,7698, 20-FEB-81,1600,300,30
7521, WARD,SALESMAN,7698, 22-FEB-81,1250,500,30
7566, JONES,MANAGER,7839, 02-APR-81,2975,20
7654, MARTIN,SALESMAN,7698, 28-SEP-81,1250,1400,30
|
partition.ctl
|
LOAD DATA
INFILE 'emp_orig.dat'
BADFILE 'emp_orig.bad'
DISCARDFILE 'emp_orig.dsc'
INSERT
INTO TABLE empp PARTITION (sal_p2) -- salary with values less than 2000
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate, sal)
|
- LOB 데이터 load
sqlldr scott/tiger control=clob.ctl log=clob.log
mybmp.lst
|
d:mybmpdatamovementimpCmdline.png
d:mybmpdatamovementimphelp.png
d:mybmpdatamovementimpIteractive.png
d:mybmpdatamovementimpParfile.png
|
clob.ctl
|
LOAD DATA
INFILE mybmp.lst
INTO TABLE MYBMP
(filename CHAR(200),
bmp LOBFILE(filename) TERMINATED BY EOF)
|
- 외부 테이블을 통한 load
먼저 로드 디렉토리를 생성하고 명령을 수행합니다.
create directory load_dir as ‘d: mp’
sqlplus scott/tiger @external.sql
샘플 emp_orig.dat
|
7369, SMITH,CLERK,7902,17-DEC-80,800,20
7499, ALLEN,SALESMAN,7698, 20-FEB-81,1600,300,30
7521, WARD,SALESMAN,7698, 22-FEB-81,1250,500,30
|
external.sql
|
create table empp (EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9),
MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2),
COMM NUMBER(7,2), DEPTNO NUMBER(2))
organization external( type oracle_loader
default directory load_dir
access parameters ( records delimited by newline
fields terminated by ','
missing field values are null
(empno, ename, job, mgr, hiredate char date_format
date mask "dd-mon-yyyy", sal, comm, deptno ))
location ('emp_orig.dat')) reject limit 5;
|
XpertMon for DB2 UDB V2
DB 사업부
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr
|
|
|
