DB2 导入导出指定的记录

lsdwyl

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.