DB2 导入导出指定的记录

本文介绍如何使用DB2的命令行工具进行数据的导出和导入操作,包括使用DEL和IXF格式导出数据到文件,以及如何将数据重新导入到数据库中。文章还提供了具体的SQL语句示例,例如按条件筛选数据进行导出,以及在导入时选择插入或替换现有数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

[color=red]Start “DB2 Command Line Processor(DB2 CLP)”, and connect to DB2 server as a privileged user who can export and import data.[/color]

(1)export TO c:\export_data\myfile.del OF [color=red]DEL[/color] 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 [color=red]IXF[/color] 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 [color=red]insert[/color] 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 [color=red]replace[/color] 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 [color=red]SELECT * FROM old table(delete from GAL_MESSAGES where (GAL_MESSAGES.LISTENER_ID = 191) and days (current date) - days (date (GAL_MESSAGES.TIMESTAMP)) >=1)[/color]

(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 [color=red]insert[/color] 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 [color=red]replace[/color] into TEST_MESSAGES

(3)
export TO c:\export_data\myfile.del OF [color=red]DEL[/color] LOBS TO c:\export_data\lob1, c:\export_data\lob2, c:\export_data\lob3 MODIFIED BY LOBSINFILE MESSAGES c:\export_data\export.txt [color=red]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[/color]

export TO c:\export_ixf\myfile.ixf OF [color=red]IXF[/color] LOBS TO c:\export_ixf\lob1, c:\export_ixf\lob2, c:\export_ixf\lob3 MODIFIED BY LOBSINFILE MESSAGES c:\export_ixf\export.txt [color=red]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[/color]

import from c:\export_data\myfile.del of [color=red]DEL[/color] 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 [color=red]insert[/color] into TEST_EXCEPTION_MESSAGE

import from c:\export_ixf\myfile.ixf of [color=red]IXF[/color] 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 [color=red]replace[/color] 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 [color=red]chardel{ coldel} MESSAGES c:\export_data\export.txt SELECT * FROM EXCEPTION_MESSAGE[/color]

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 [color=red]chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt insert into TEST_EXCEPTION_MESSAGE[/color]
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 [color=red]chardel{ coldel} COMMITCOUNT 100 MESSAGES c:\export_data\import.txt replace into TEST_EXCEPTION_MESSAGE[/color]

[b]And please use “replace” instead of “insert” if you want to replace data, which will delete data firstly and then insert data into database.[/b]

[color=red][b]If you want to know more information about “DB2 import and export command useage”, you can visit [url]http://www.database-books.us/db2_0001.php[/url] to download the book, and then you can see page 362 and page 450.[/b][/color]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值