DB2 export and import

Export匯出過程
db2 ? export
EXPORT TO filename OF {IXF | DEL | WSF}
[LOBS TO lob-path [ {,lob-path} ... ] ]
[LOBFILE lob-file [ {,lob-file} ... ] [MODIFIED BY {filetype-mod ...}]
[METHOD N ( column-name [ {,column-name} ... ] )] [MESSAGES message-file]
{select-statement | HIERARCHY {STARTING sub-table-name |
(sub-table-name [{, sub-table-name} ...])} [WHERE ...] }
  filetype-mod：
    NODOUBLEDEL、LOBSINFILE、CHARDELx、COLDELx、DLDELx、DECPLUSBLANK、
DECPTx、DATESISO、1、2、3、4、CODEPAGE=x、STRIPLZEROS 和 NOCHARDEL

o        以空白作為正十進位制值的字首（DECPLUSBLANK）
o        使用 ISO 日期格式（DATESISO）
o        不識別雙字元定界符（NODOUBLEDEL）

  檔案型別修飾符
CHARDELx：指定x為新的單字串定界符。預設值是雙引號（“”）
COLDELx ：指定x為新的單字元列定界符。預設值是雙引號（，）
DLDELx：十進位制小數位字元（" % &  ( ) * . / : ; < = > ? |  ,  '  _
CODEPAGE=x
指定x這個ASCII字串為輸出資料的新內碼表，在匯出操作期間，將字元資料從應用程式內碼表轉換成這一內碼表：
  Timestampformat=”x”  x 是源表中時間戳記的格式。(YYYY/MM/DD HH:MM:SS.UUUUUU、YYYY/MM/DD HH、YYYY-MM-DD HH:MM:SS TT、MMM DD YYYY HH:MM:SS:UUUTT、MMM DD YYYY HH:MM:SSTT

Export to myfile.del of del modified by chardel! coldel@ codepage=1208 timestampformat=”yyyy.mm.dd hh:mm tt” select_statment
捕捉錯誤或警告資訊
Messages x:\檔名

1.        非定界或定長ASCII （ASC）
2.        定界ASC (DEL)
最主要的分隔符有以下幾種：
字元分隔符
列分隔符
行分隔符：標識一行或一個記錄的結束。DB2用新行符XOA（UNIX）界定分隔符：db2用換行符XODOA作為界定分隔符。
3.        PC/IXF檔案
4.        工作表格式WSF

例一：Export to myname.del of del select * from myname
格式：export to x.ixf of ixf … select * from X…
例二
   db2 export to org.del of del modified by chardel! coldel@ codepage=1208  select * from org
字串由感嘆號！括起來，列由@號定界，字串被轉換成內碼表1208
例二
   db2 export to org.del of del modified by chardel! coldel@ codepage=1208 messages msg.out select * from org
字串由感嘆號！括起來，列由@號定界，字串被轉換成內碼表1208,新增message引數後，產生一個msg.out檔案，捕獲匯出期間錯誤，警告和資訊性訊息。
例三
   匯出大物件
   Export to file_name of file_type lobs to lobfile_directory,lobfile_directory_2,….
           Lobfile lobfilename
           Modified by lobsinfile select_statment
   有了lobsinfile修飾符，export實用程式就查詢lobs to 子句中指定的目錄，然後將lob資料放在那裡。如果沒有找到lobs to 子句，就將lob資料傳送到當前工作目錄。


例四
  CONNECT TO SAMPLE;
EXPORT TO "E:\db2log\org.ixf" OF IXF METHOD N (DEPTNUMB, DEPTNAME) MESSAGES "E:\db2log\msg.out" SELECT * FROM ADMINISTRATOR.ORG;
CONNECT RESET;
例五
CONNECT TO SAMPLE;
EXPORT TO "E:\db2log\org1.ixf" OF IXF MESSAGES "E:\db2log\msg1.out" SELECT * FROM ADMINISTRATOR.ORG;
CONNECT RESET;

Export Sessions - CLP Examples
The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format. If the database connection is not through DB2 Connect, the index definitions (if any) will be stored in the output file; otherwise, only the data will be stored:
   db2 export to myfile.ixf of ixf messages msgs.txt select * from staff
The following example shows how to export the information about employees in Department 20 from the STAFF table in the SAMPLE database (to which the user must be connected) to awards.ixf, with the output in IXF format:
   db2 export to awards.ixf of ixf messages msgs.txt select * from staff
      where dept = 20
The following example shows how to export LOBs to a DEL file:
   db2 export to myfile.del of del lobs to mylobs/
      lobfile lobs1, lobs2 modified by lobsinfile
      select * from emp_photo
The following example shows how to export LOBs to a DEL file, specifying a second directory for files that might not fit into the first directory:
   db2 export to myfile.del of del
      lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
      select * from emp_photo
The following example shows how to export data to a DEL file, using a single quotation mark as the string delimiter, a semicolon as the column delimiter, and a comma as the decimal point. The same convention should be used when importing data back into the database:
   db2 export to myfile.del of del
      modified by chardel'' coldel; decpt,
      select * from staff


db2 import匯入過程
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], ...)

1.        增加資料
Import from aaa.del of del messages msg.out insert into product
Import from aaa.del of del messages msgout insert into product(price,prod_no,description)
2.        更新已經存在的資料及加入新資料
       Import from aaa.del of del messages msg.out replace into product


3.        替換資料
    Import from aaa.del of del messages msg.out replace_create into product
4.        建立表
      Import from aaa.ixf of ixf messages msg.out replace_create into product

5.        commitcount與restartcount選項
import from myfile.ixf of ixf commitcount 500 messages msg.out insert into newtable
import from myfile.ixf of ixf commitcount 50 restartcount 2000 messages msg.out insert into newtable
6.        匯入大物件