?????. 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
|
|
|