【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

db2 => export to d:\employee.wsf of wsf messages d:\employee_wsf.msg select * fr

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" 行。

結果如下：

"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

更多資訊，請參考：

<a href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0405melnyk/">http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0405melnyk/</a>