【DB2】db2命令Export与Import

wsf工表表

关于3种导入导出操作进行简单的介绍:
export：导出数据，支持IXF，DEL或WSF
import：导入数据，可以向表中导入数据，支持上面提到的4种文件类型。   
load：导入数据，功能和import基本相同。支持以上说的几种文件类型。

Export与Import注意事项

<1>不同字符集、字段含有TIMESTAMP格式

导出

qinys@Linux:~> db2 "export to Exp.dat of del MODIFIED BY CODEPAGE=1386 TIMESTAMPFORMAT=\"yyyy-mm-dd hh:mm:ss tt\" SELECT * FROM tb1"
SQL3104N  The Export utility is beginning to export data to file "Exp.dat".

SQL3105N  The Export utility has finished exporting "2" rows.


Number of rows exported: 2

CODEPAGE=1386 是指在数据导出时，做一个数据库代码页的转换

1208→GBK

1386→UTF-8

导入

qinys@Linux:~> db2 "import from Exp.dat of del MODIFIED BY CODEPAGE=1386 TIMESTAMPFORMAT=\"yyyy-mm-dd hh:mm:ss tt\"  INSERT INTO tb1_tmp";
SQL3109N  The utility is beginning to load data from file "Exp.dat".

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "2".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "2" rows were processed from the input file.  "2" rows were
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2

Import导入大量数

db2 import from 数据文件名 of ixf modified by compound=100 insert into 表名

上面的命令中IMPORT会在每100条记录而不是每条记录插入后等待返回的SQL执行结果。

设置commitcount 参数加快导入

db2 import from 数据文件名 of ixf modified by compound=100 commitcount 10000 insert into 表名

【DB2】db2命令Export与Import

标签：ext   com   any   hang   tco   back   mes   参数   sof