【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/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-674294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-674294/