?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
???? ORACLE? DB2 UDB? ??? ??? ??? ???? DB2 UDB? ??? ??
?????. ? IMPORT, LOAD? ?? ????? ?????.
DB2 - import
DB2??? Oracle? imp? ??? IMPORT ????? ?????.
import? export ? ???? ?? ??? 4?? ???. ??? ??? ??? ????,
??? ???? ?? ?? ?????. ????? ???? ?? ??? ? ? ???
.ixf? .del, .asc ? ?????. ?? ??, employee.ixf ? ?? ????
DB2 interchange ??? ?? ????.
???? 4?? ?? ??? ??? ????.
- Delimited ASCII files : DEL
? ???? ???? ?? ???, ?? ???, ? ???? ??? ? ? ????.
?? ???? ??? ????("), ?? ???? ??? ??(,) ???.
?? ???? ??? ? ????.
? ???? UNIX?? hex code? X'0A'??, WINDOWS ??? X'0D0A'???.
-
Non-delimited ASCII file : ASC
Non-Delimited ASCII file ? ???? ??? ?? ?????.
???? ?? row? ??? ?????. ????? ? ??? ? ?? ??? ???
???? ?? application ?? ??? ??? ?????.
- Integrated Exchange Format file : IXF
DB2 ???????? ???? ??? ? ?? ?????. IXF ??? ??????
??? ? ???? ????? ???? ????. ? ??? ??? ???? ??
?? ? ? ????.
- Worksheet Format file : WSF
? ??? ????? Lotus 1-2-3 ? Symphony ???? ??? ??? ?????.
DB2? import ? ???? ???? ??? ??? ??? ???. import? ????
???? SYSADM ?? ?? DBA ??? ?? ???, ??? ?? CREATETAB, CONTROL,
SELECT, INSERT ?? ??? ?????.
DB2 UDB? import ??? ???(straightforward)???. ??? ??? ?????
?? ???, ????? db2 "? Import" ??? ??????.
|
IMPORT FROM filename OF {IXF | ASC | DEL | WSF}
[LOBS FROM lob-path [ {,lob-path} ... ] ] [MODIFIED BY filetype-mod ...]
[METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
[NULL INDICATORS (col-position [ {,col-position} ... ] )] |
N ( col-name [ {,col-name} ... ] ) |
P ( col-position [ {,col-position} ... ] )}]
[ALLOW {NO | WRITE} ACCESS]
[COMMITCOUNT {n | AUTOMATIC}] [{RESTARTCOUNT | SKIPCOUNT} n]
[ROWCOUNT n] [WARNINGCOUNT n] [NOTIMEOUT] [MESSAGES message-file]
{{INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE}
INTO {table-name [( insert-column , ... )] | hierarchy-description}
| CREATE INTO {table-name [( insert-column , ... )] |
hierarchy-description {AS ROOT TABLE | UNDER sub-table-name}
[IN tablespace-name [INDEX IN tablespace-name] [LONG IN tablespace-name]]}
[datalink-specification]
filetype-mod:
COMPOUND=x, INDEXSCHEMA=schema, FORCEIN, INDEXIXF, IMPLIEDDECIMAL,
NOCHECKLENGTHS, NOEOFCHAR, NULLINDCHAR, RECLEN=x, STRIPTBLANKS,
STRIPTNULLS, NO_TYPE_ID, NODOUBLEDEL, LOBSINFILE, USEDEFAULTS,
CHARDELx, COLDELx, DLDELx, DECPLUSBLANK, DECPTx, DATESISO,
DELPRIORITYCHAR, IDENTITYMISSING, IDENTITYIGNORE,
GENERATEDMISSING, GENERATEDIGNORE, DATEFORMAT=x, TIMEFORMAT=x,
TIMESTAMPFORMAT=x, KEEPBLANKS, CODEPAGE=x, NOROWWARNINGS,
NOCHARDEL, USEGRAPHICCODEPAGE
hierarchy-description:
{ALL TABLES | (sub-table-name [(insert-column, ...)], ...)} [IN]
HIERARCHY {STARTING sub-table-name | (sub-table-name, ...)}
datalink-specification:
([DL_LINKTYPE URL][{DL_URL_REPLACE_PREFIX prefix |
DL_URL_DEFAULT_PREFIX prefix}] [DL_URL_SUFFIX suffix], ...)
|
DB2 ?? import ? ???? ??? 3??? ????.
- ???? : GUI? ?? ??
????? ?????, ???? ? ??? ?????? ???? ?????.
SAMPLE ??????? ????? ???? import ? ???? ??? ?????.
Example 1. Importing a comma delimited files, employee.del into the existing employee table
|
"000010","CHRISTINE","I","HAAS","A00","3978",19650101,"PRES ",18,"F",19330824, ...
"000020","MICHAEL","L","THOMPSON","B01","3476",19731010,"MANAGER ",18,"M",19480202, ...
"000030","SALLY","A","KWAN","C01","4738",19750405,"MANAGER ",20,"F",19410511, ...
"000050","JOHN","B","GEYER","E01","6789",19490817,"MANAGER ",16,"M",19250915, ...
"000060","IRVING","F","STERN","D11","6423",19730914,"MANAGER ",16,"M",19450707, ...
|
????? ???? ???? "??" --> "????" --> "IBM DB2" -->
"?? ?? ??" --> "?? ??" ? ?????. SAMPLE ??????? ??? ?
"Tables" ? ?????. ?? ??? ???? ??? ???? employee ????
??? ???? ???? ???? import ? ?????.
Import ? ??? ??? del ???? ???, ?? ??? ?? ??? import ??? ?
??? ???. ???? ?????? employee.del ??? ????, ?? ???
????? ?????. Import ???? INSERT , INSERT_REPLACE , REPLACE ???
????. INSERT? ???? ??? ???? ??? ???? ???? ???.
INSERT_UPDATE? ????? ????? ??? ??? ?? ?? ??? ???.
??? ???? ????, ??? ?? ???? ?????. REPLACE ???
????, ??? ???? ?? ???? ??? ??? insert ???.
??. 8 Import - the file tab
Example. 2. ?? ???? ??? 2 ??? ?? del ??? ???????.
INSERT_UPDATE ??? ???? import ??? ???????.
??? ??? '*'? ???? ?????.
|
"000010"*"CHRISTINE"*"I"*"HAAS"*"A00"*"3978"*19650101*"PRES "*18*"F"*19330824* ...
"000020"*"MICHAEL"*"L"*"THOMPSON"*"B01"*"3476"*19731010*"MANAGER "*18*"M"*19480202* ...
"000030"*"SALLY"*"A"*"KWAN"*"C01"*"4738"*19750405*"MANAGER "*20*"F"*19410511* ...
"000050"*"JOHN"*"B"*"GEYER"*"E01"*"6789"*19490817*"MANAGER "*16*"M"*19250915* ...
"000060"*"IRVING"*"F"*"STERN"*"D11"*"6423"*19730914*"MANAGER "*16*"M"*19450707* ...
|
???? ??? Example 1. ? ???, ???? DEL ?? ??? ???? ????
??? ??? ???. COLDEL ? ????, '*'? ?????.
??. 9 ??? ??
???? ??? ? ?? ??? ???? �??� ??? ????.
???? import ? ??? ?????.
??. 10 ?? ?? 1
BONUS, SALARY? ??? ??? ??? ?? ?????.
??. 11 ?? ?? 2
INSERT_UPDATE? ??? ?? ??? ??, ?? ?? ??? ??? ????.
???? ??? ?? SQL??? ???? ??? ??? ???.
alter table employee add constraint empno primary key (empno)
- ??? ???(CLP) : ???? ?? ??
Example 1. ??? ??? employee ???? ','? ???? ????
?????? employee.del? ???????.
????? ??? ??? ??????.
|
CONNECT TO SAMPLE;
IMPORT FROM "E: mp1employee.del" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) MESSAGES "E: mp1employee.log"
INSERT INTO ADMINISTRATOR.EMPLOYEE
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM);
CONNECT RESET;
|
Example 2. ?? ???? '*'??, ??? ? ??? ??? ??? ???
import ? ??? ????? ??? ??? ??????.
|
CONNECT TO SAMPLE;
IMPORT FROM "E: mp1employee.del" OF DEL MODIFIED BY COLDEL*
METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
MESSAGES "E: mp1employee.log"
INSERT_UPDATE INTO ADMINISTRATOR.EMPLOYEE
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY);
CONNECT RESET;
|
DB2 - LOAD
Import ??? DB2??? ??? ???? DB2 UDB ??????? ?? ?? ????
LOAD ?? ????? ?????. Import ? insert SQL ?? ???? ?? ??
LOAD? ?????? ??? ?? ???? ??? ?????. Import? LOAD ??
??? ?? ???(UDT)? LOB? ?????, LOAD? ???? ????? ????
??? ? ????. ? ? ?? ?? ?? ???? ??? ?? ??? ??? ????.
LOAD? import ?? ?? ????.
LOAD? ??? ???? ?? ??? ??? ????. ??? ???? ? ???
?????. load ?? LIST UTILITIES ?? DB2 ???? ???? load ???
??? ? ????. LOAD? ??? ??? ??? ????? load ?? ????
?? ???? ??? ? ????.
LOAD ? ?????, SYSADM, DBADM ?? SYSMAINT ??? ??? ???,
load ??? ?? load ??? ?? INSERT? DELETE ??? ??? ??? ???.
LOAD ??? ?? ??? ??? ???, ?? ??? db2 "? Load" ????
?? ? ? ????.
|
LOAD QUERY TABLE table-name [TO local-message-file]
[NOSUMMARY | SUMMARYONLY] [SHOWDELTA]
LOAD [CLIENT] FROM file/pipe/dev/cursor_name [ {,file/pipe/dev} ... ]
OF {ASC | DEL | IXF | CURSOR}
[LOBS FROM lob-path [ {,lob-path} ... ] ]
[MODIFIED BY filetype-mod [ {filetype-mod} ... ] ]]
[METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
[NULL INDICATORS (col-position [ {,col-position} ... ] )]
| N ( col-name [ {,col-name} ... ] )
| P ( col-position [ {,col-position} ... ] )}]
[SAVECOUNT n]
[ROWCOUNT n] [WARNINGCOUNT n] [MESSAGES msg-file]
[TEMPFILES PATH pathname]
{INSERT | REPLACE | RESTART | TERMINATE}
INTO table-name [( insert-column [ {,insert-column} ... ] )]
[datalink-specification] [FOR EXCEPTION table-name]
[STATISTICS {NO | USE PROFILE}]
[{COPY {NO | YES { USE TSM [OPEN num-sess SESSIONS]
| TO dir/dev [ {,dir/dev} ... ]
| LOAD lib-name [OPEN num-sess SESSIONS]}}
| NONRECOVERABLE} ]
[HOLD QUIESCE] [WITHOUT PROMPTING] [DATA BUFFER buffer-size]
[SORT BUFFER buffer-size] [CPU_PARALLELISM n] [DISK_PARALLELISM n]
[INDEXING MODE {AUTOSELECT | REBUILD | INCREMENTAL | DEFERRED}]
[CHECK PENDING CASCADE {DEFERRED | IMMEDIATE}]
[ALLOW NO ACCESS | ALLOW READ ACCESS [USE tblspace-name]] [LOCK WITH FORCE]
[[PARTITIONED DB CONFIG] partitioned-db-option [{partitioned-db-option}...]]
filetype-mod:
NOROWWARNINGS, ANYORDER, BINARYNUMERICS, CODEPAGE=x,
DUMPFILE=x, FASTPARSE, NOHEADER, TOTALFREESPACE=x,
INDEXFREESPACE=x, PAGEFREESPACE=x, FORCEIN, IMPLIEDDECIMAL,
PACKEDDECIMAL, NOCHECKLENGTHS, NOEOFCHAR, NULLINDCHAR=x,
RECLEN=x, STRIPTBLANKS, STRIPTNULLS, NODOUBLEDEL, LOBSINFILE,
CHARDELx, COLDELx, DLDELx, DECPLUSBLANK, DECPTx, DATESISO,
DELPRIORITYCHAR, USEDEFAULTS, DATEFORMAT=x, TIMEFORMAT=x,
TIMESTAMPFORMAT=x, ZONEDDECIMAL, KEEPBLANKS, IDENTITYMISSING,
IDENTITYIGNORE, IDENTITYOVERRIDE, GENERATEDMISSING,
GENERATEDIGNORE, GENERATEDOVERRIDE, USEGRAPHICCODEPAGE
partitioned-db-option:
HOSTNAME x, FILE_TRANSFER_CMD x, PART_FILE_LOCATION x, OUTPUT_DBPARTNUMS x,
PARTITIONING_DBPARTNUMS x, MODE x, MAX_NUM_PART_AGENTS x, OMIT_HEADER,
ISOLATE_PART_ERRS x, STATUS_INTERVAL x, PORT_RANGE x, CHECK_TRUNCATION,
MAP_FILE_INPUT x, MAP_FILE_OUTPUT x, TRACE x, NEWLINE, DISTFILE x
datalink-specification:
([DL_LINKTYPE URL][{DL_URL_REPLACE_PREFIX prefix |
DL_URL_DEFAULT_PREFIX prefix}] [DL_URL_SUFFIX suffix], ...)
|
LOAD ??? 4??? ??? ? ????. ? 4??? ????? ?????.
- ?? ?? (load phase) : ? ????, ?? ??? ??? ???? ????,
???? ?? ? ?????. ? ???? ??? ??? LOAD ??? ???
savepoint? ???? ????. savepoint? LOAD? ???? ??,
? ??(reload) ??? ??? ? ?????. ???? ?? ????
?????? ?????.
- ?? ?? (build phase) : ? ????? ???? ?????.
? ???? failure? ???? ??, LOAD ??? ?? ????
?? ???? ???? ???.
- ?? ?? (delete phase) : ? ????? ?? ?? ?? ??
????? ???? ??? ?????. ?? ??? exception table?
?????. ? ???? failure? ???? LOAD ?? ???? ?? ????
???? ???. ???? ? ???? ?????.
- ??? ?? ?? (index copy phase) : ? ???? ???? ??? ??
????????? ?? ??? ????? ?????. ?? ???? ???
??? ?? ??? ?? ??? ????? ??? ???? ?????.
? ????? ???? ?????.
LOAD ??? ???? ?? ? ??? ?????. LOAD ?? ??? LOAD ???
??? ??(intermediate) ???? ?????. ??? ?? ?? CURSOR ???
?? ?????. ? ??? ??? ?? ??? ???? ????.
- WARNINGCOUNT : LOAD? ?? ??? ?? ?????. n ?? ???? ? ?? ??? ?????.
- CPU_PARALLELISM : CPU? degree of parallelism? ?????. ???? DB2? ????? ??? ?? ???? ?????.
- DISK_PARALLELISM : ???? degree of parallelism? ?????. ? ?? ??? ???? ?? ?????. ???? DB2? ????? ??? ?? ???? ?????.
- DATA BUFFER : LOAD ??? ??? 4K ??? ?? ?????.
- TEMPFILES PATH : LOAD ?? ? ??? ?????? ??? ??? ?????.
- FOR EXCEPTION : ?? ?? ??? ???? ?????.
- DUMPFILE : ??? ?? ?? ??? ????? ??? ?? ?? ?????.
- NOROWWARNING : ??? ?? ?? ??? ???? ??? ?????.
LOAD? LOAD?? ?? ???? ???? ?? ??? ??? ? ?? ?????
?????.
- PAGEFREESPACE : ? ??? ???? ????(%)? ?????. ?????? DB2? ????? ???.
- INDEXFREESPACE : ? ??? ???? ????(%)? ?????. ?????? DB2? ????? ???.
- TOTALFREESPACE : ?????? ???? ??? ??? ??(%)
?? ???? ???? DB2 UDB Data Movement Utilities Guide and Reference?
????? ????.
??? ?? ???? LOAD? ??? ?????? ?? ???????.
IMPORT??, LOAD? ???? ?? GUI? ???? ??, ???? ???? ??,
??? API? ??? ??? 3??? ????. ???? GUI? ???? ??
???? ???????.
- GUI? ???? ??
Example .1 - ???? del ??? LOAD ???.
Import ??? ??? employee.del ??? ????? ?????.
????? ?? ???? ??
"??" --> "????" --> "IBM DB2" --> "?? ?? ??" --> "????"?
?????.
?????? ???? sample ? ?????. ??? ??? ? EMPLOYEE
???? ??? ???? ???? ???? "load"? ?????.
??. 12 ????? ??? load
???? append ??? ? ???, replace ??? ? ???? ??? ? ???,
append ??? ?? ? ???? ??? ??? ? ????.
??. 13 Append / Replace ??
??? ?? ?? ??? ???? ????, ??? ??? ??? ?????.
??? ?? ? ??????? ??? ??? remote? ???? ?????.
??. 14 ??? ??? ??
?? ????? BONUS ? COMM ??? ??? ??? ???? ????? ?????.
??. 15 ??? ??? ?? ??
??? ??? ? ? ?? ???? ????. ? ????? ???? ?????
?????.
??. 16 ??? ????
?? ???? ??? ??? ??? ?? ???. ???? ????? ?????.
??. 17 Fail, ?? ??
CPU parallelism, ???? ?? ?? ??? ?????.
??. 18 ?? ?? ??
??? ????? ???? ???? "Finish"? ???????.
??. 19 ??? / ?? ?? ??
??? ???? ?????? ?????.
??. 20 ?? ???
Employee.msg ??? ???? ?? ??, ??? ??, ??? ??, ??? ??,
commit ? ? ? ?? ?? ? ? ????.
- ?? ?? ???? ??
GUI? ?? ??? ?? ??? ?? ?? ?? ???? ?? ?? ?????.
???? ??? ????.
|
CONNECT TO SAMPLE;
LOAD FROM "E: mp1employee.del" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
MESSAGES "E: mp1employee.msg"
INSERT INTO ADMINISTRATOR.EMPLOYEE
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY)
COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET;
|
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
|
|
|
