Home > Dataroom > News Letter
 
Download
News Letter
Site Link
XpertMon ???? 39?
2007/03/22 10:38 12080


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

▲ XpertMon ???? 40? iteg 2007-03-22 11822
- XpertMon ???? 39? iteg 2007-03-22 12080
▼ XpertMon ???? 38? iteg 2007-03-22 11572
List Write Modify