DB2 导入导出指定的记录

最新推荐文章于 2021-08-28 12:34:45 发布

iteye_13110

最新推荐文章于 2021-08-28 12:34:45 发布

阅读量288

点赞数

CC 4.0 BY-SA版权

分类专栏： DB2 文章标签： DB2 C C++ C# PHP

本文链接：https://blog.youkuaiyun.com/iteye_13110/article/details/81826572

DB2 专栏收录该内容

11 篇文章

订阅专栏

本文介绍如何使用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]