【DB2學習】遷移資料之EXPROT

db2 => connect to sample
   資料庫連線資訊

 資料庫伺服器         = DB2/NT 9.5.0
 SQL 授權標識         = YANG
 本地資料庫別名       = SAMPLE
db2 => export to d:\employee.ixf of ixf messages d:\employee.msg select * from a
dministrator.employee
匯出的行數：42

db2 => export to d:\employee.del of del messages d:\employee_del.msg select * fr
om administrator.employee
匯出的行數：42

db2 => export to d:\employee.wsf of wsf messages d:\employee_wsf.msg select * fr
om administrator.employee
匯出的行數：42

EXPROT 支援三種檔案格式 IXF ,DEL,WSF;雖然export不支援ASC檔案型別，但是可以透過修改select 語句而生成ASC 型別的資料檔案 給個例子：

db2 => export to d:\employee.del of del select firstnme concat empno from administrator.employee
SQL3104N  EXPORT 實用程式 正在開始將資料匯出至檔案 "d:\employee.del"。
SQL3105N  Export 實用程式已經完成匯出 "42" 行。
匯出的行數：42
結果如下：
"CHRISTINE000010"
"VINCENZO000110"
"SEAN000120"
"DIAN200010"
"GREG200120"
"MICHAEL000020"
"SALLY000030"
"DELORES000130"
"HEATHER000140"
"KIM200140"
"IRVING000060"
"BRUCE000150"
"ELIZABETH000160"

當匯入資料時，第一列的起始位置為2，這樣可以忽略掉第一個位置的雙引號 " 字元。

匯出時派生別名：不過只有IXF,WSF這兩種格式支援。

db2 => export to d:\staff.ixf of ixf select salary-comm as pay from administrator.staff
SQL3104N  EXPORT 實用程式 正在開始將資料匯出至檔案 "d:\staff1.ixf"。
SQL27984W  成功地完成 Export 命令。在匯出期間，尚未將某些重新建立資訊儲存至
PC/IXF 檔案。此檔案在 Import CREATE 方式下將不受支援。原因碼 = "7"。
SQL3105N  Export 實用程式已經完成匯出 "70" 行。

匯出的行數：70
還可以這樣寫
db2 =>export to d:\staff.ixf of ixf method ('pay',...) select salary-comm ,... from administrator.staff

--處理十進位制資料列使用 decpt跟上一個特殊字元 可以修改小數點定界符。
未修改之前
10,"Sanders",20,"Mgr  ",7, 98357.50,
20,"Pernal",20,"Sales",8, 78171.25, 00612.45
30,"Marenghi",38,"Mgr  ",5, 77506.75,
40,"O'Brien",38,"Sales",6, 78006.00, 00846.55
50,"Hanes",15,"Mgr  ",10, 80659.80,
60,"Quigley",38,"Sales",, 66808.30, 00650.25
修改之後的效果
db2 =>export to d:\staff2.del modified by decpt# messages d:\staff.msg select * from administrator.staf
10,"Sanders",20,"Mgr  ",7,+98357#50,
20,"Pernal",20,"Sales",8,+78171#25,+00612#45
30,"Marenghi",38,"Mgr  ",5,+77506#75,
40,"O'Brien",38,"Sales",6,+78006#00,+00846#55
50,"Hanes",15,"Mgr  ",10,+80659#80,
--處理日期：del 和wsf 預設的日期格式為 yyyymmdd 使用者可以透過使用datesiso 來將日期格式修改為yyyy-mm-dd
db2 => export to d:\sales.del of del modified by datesiso select * from a
dministrator.sales

"2005-12-31","LUCCHESSI","Ontario-South",1
"2005-12-31","LEE","Ontario-South",3
"2005-12-31","LEE","Quebec",1
"2005-12-31","LEE","Manitoba",2
"2005-12-31","GOUNOT","Quebec",1
"2006-03-29","LUCCHESSI","Ontario-South",3
"2006-03-29","LUCCHESSI","Quebec",1
"2006-03-29","LEE","Ontario-South",2
更多資訊，請參考：
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0405melnyk/