DB2数据库导入导出

2022-12-03 21:28:50

数据移动格式

• ASC\DEL 文本文件。

• WSF 工作表格式主要用于LOTUS软件。

• IXF 集成交换格式。

ASC,DEL,WSF在跨平台可能会导致数据丢失.跨平台建议使用IXF。

导出工具-EXPORT

•Export用于将数据从数据表中导出几种格式的文件中。其实，它执行了一个SQL 的SELECT操作。

•支持DEL,IXF,WSF,不支持ASC。

BD2命令行,如:

C:\>db2 connect to sample

C:\>db2 export to “C:\test\employee_bak.ixf” of ixf “select * from employee”

Message是输出日志，也可以不要。

在查询窗口中执行如下：

CONNECT TO SAMPLE;
EXPORT TO "C:\test\employee_bak.ixf" OF IXF MESSAGES "C:\test\export_log.txt" SELECT * FROM ADMINISTRATOR.EMPLOYEE;
CONNECT RESET;

控制中心:点击Export选项之后会出现一个向导，根据向导提示操作即可一步步的完成导出操作

(Import,Load也可以通过控制中心执行)。

2. 导入工具

•IMPORT

支持DEL,IXF,WSF,ASC。

•LOAD

支持DEL,IXF,ASC,不支持WSF。

区别:Import可以创建表和索引,支持触发器记录日志,但速度较慢。Load导入时表必须存在,不支持触发器,不记录日志,速度较快。

•Import用于将数据文件导入到数据表中，主要方式如下：

IMPORT 导入测试

C:\Program Files\IBM\SQLLIB\BIN>db2 "drop table employee" --删除表

C:\Program Files\IBM\SQLLIB\BIN>db2 import from "C:\test\SAMPLE_Employee_bak.ixf" OF IXF REPLACE_CREATE into employee --导入还原

导入到新表employee2：

C:\Program Files\IBM\SQLLIB\BIN>db2 create table employee2 like employee --创建一个空的employee2表
DB20000I The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from employee2

EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB
EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ---------- --------
------- --- ---------- ----------- ----------- -----------

0 record(s) selected.

C:\Program Files\IBM\SQLLIB\BIN>

C:\Program Files\IBM\SQLLIB\BIN>db2 import from "C:\test\SAMPLE_Employee_bak.ixf" OF IXF insert into employee2
SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date
"20191027", and time "030644".

SQL3153N The T record in the PC/IXF file has name
"c:\test\SAMPLE_Employee_bak.ixf", qualifier "", and source " ".

SQL3109N The utility is beginning to load data from file
"C:\test\SAMPLE_Employee_bak.ixf".

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

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

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

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

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

C:\Program Files\IBM\SQLLIB\BIN>db2 "select count(*) from employee2"

1
-----------
42

1 record(s) selected.

LOAD工具导入测试

Load不能创建表及索引,故不能使用Create方式。

主要方式:

C:\Program Files\IBM\SQLLIB\BIN>db2 load from "C:\test\SAMPLE_Employee_bak.ixf" of ixf replace into employee2

C:\Program Files\IBM\SQLLIB\BIN>db2 create table employee3 like employee

C:\Program Files\IBM\SQLLIB\BIN>db2 load from "C:\test\SAMPLE_Employee_bak.ixf"
of ixf insert into employee3

码农公寓

相关文章