Start “DB2 Command Line Processor(DB2 CLP)”, and connect to DB2 server as a privileged user who can export and import data.
(1)export TO c:\export_data\myfile.del OF
DEL LOBS TO c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE MESSAGES c:\export_data\export.txt SELECT * FROM GAL_MESSAGES where (GAL_MESSAGES.LISTENER_ID = 191) and days (current date) - days (date (GAL_MESSAGES.TIMESTAMP)) >=1
export TO c:\export_data\myfile.ixf OF
IXF LOBS TO c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE MESSAGES c:\export_data\export.txt SELECT * FROM GAL_MESSAGES where (GAL_MESSAGES.LISTENER_ID = 191) and days (current date) - days (date (GAL_MESSAGES.TIMESTAMP)) >=1
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
insert into TEST_MESSAGES
or
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
replace into TEST_MESSAGES
export TO c:\export_data\myfile.del OF DEL LOBS TO c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE MESSAGES c:\export_data\export.txt
SELECT * FROM old table(delete from GAL_MESSAGES where (GAL_MESSAGES.LISTENER_ID = 191) and days (current date) - days (date (GAL_MESSAGES.TIMESTAMP)) >=1)
(2)
export TO c:\export_data\myfile.del OF DEL LOBS TO c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE chardel{ coldel} MESSAGES c:\export_data\export.txt SELECT * FROM GAL_MESSAGES
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
insert into TEST_MESSAGES
or
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2 MODIFIED BY LOBSINFILE chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
replace into TEST_MESSAGES
(3)
export TO c:\export_data\myfile.del OF
DEL LOBS TO c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE MESSAGES c:\export_data\export.txt
SELECT * FROM EXCEPTION_MESSAGE where EXCEPTION_CODE_ID in (select EXCEPTION_CODE_ID from EXCEPTION_CODE where PROJECT_ID=0 and PROJECT_ID in (select PROJECT_ID from GEH_PROJECT)) and days (current date) - days (date (EXCEPTION_MESSAGE.MESSAGE_TIME)) >=5
export TO c:\export_ixf\myfile.ixf OF
IXF LOBS TO c:\export_ixf\lob1, c:\export_ixf\lob2, c:\export_ixf\lob3 MODIFIED BY LOBSINFILE MESSAGES c:\export_ixf\export.txt
SELECT * FROM EXCEPTION_MESSAGE where EXCEPTION_CODE_ID in (select EXCEPTION_CODE_ID from EXCEPTION_CODE where PROJECT_ID=0 and PROJECT_ID in (select PROJECT_ID from GEH_PROJECT)) and days (current date) - days (date (EXCEPTION_MESSAGE.MESSAGE_TIME)) >=5
import from c:\export_data\myfile.del of
DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
insert into TEST_EXCEPTION_MESSAGE
import from c:\export_ixf\myfile.ixf of
IXF LOBS FROM c:\export_ixf\lob1, c:\export_ixf\lob2, c:\export_ixf\lob3 MODIFIED BY LOBSINFILE COMMITCOUNT 100 MESSAGES c:\export_ixf\import.txt insert into EXCEPTION_MESSAGE
or
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE COMMITCOUNT 100 MESSAGES c:\export_data\import.txt
replace into TEST_EXCEPTION_MESSAGE
(4)
export TO c:\export_data\myfile.del OF DEL LOBS TO c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE
chardel{ coldel} MESSAGES c:\export_data\export.txt SELECT * FROM EXCEPTION_MESSAGE
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE
chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt insert into TEST_EXCEPTION_MESSAGE
or
import from c:\export_data\myfile.del of DEL LOBS FROM c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE
chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt replace into TEST_EXCEPTION_MESSAGE
And please use “replace” instead of “insert” if you want to replace data, which will delete data firstly and then insert data into database.
If you want to know more information about “DB2 import and export command useage”, you can visit http://www.database-books.us/db2_0001.php to download the book, and then you can see page 362 and page 450.
分享到:
相关推荐
db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出db2_导入导出
DB2数据库导出表结构与导入、导出表数据
文档是自己测试后空闲时记录下来的,描述将用户的DB2上的一个数据库导出并导入到自己的DB2上的操作方法和记录,DB2没有直接的数据库导出,需导出表结构、表数据,然后在导入,详见文档。
Db2文件导入导出常见命令总结,帮助db2新手解决文件导入导出的问题。
DB2导出、导入表结构、表数据及数据库安装小结(Windows环境).docx
DB2导入导出功能,增删改查 增删改查
手把手,DB2数据导入导出详解,下了不后悔
DB2导入、导出、备份及恢复数据库等操作资料!
DB2EMS数据库导入导出,可以更好的帮助你学习DB2的导入导出
DB2 的数据库的导入、导出 使用的是DB2的控制中心
db2look导出存储过程脚本,
DB2使用经验积累-导入导出-编程-函数-sql优化
DB2常用命令,备份数据库,数据导入导出
DB2导出数据库表结构和数据.pdf
本文将详细提供db2导入导出单表数据及db2备份恢复等相关一些操作命令汇总,有需求的朋友可以参考
导出db2数据库的方法,里面提供了几种不同的方法
db2数据导出工具,比较方面实用,操作方面