DB2 Export

"Laughter and Love are mankind's most valuable Exports" - Wikiquote.com

Exports data from a database to one of several external file formats.

ExportFileFormats

1. DEL (delimited ASCII format)
2. WSF (work sheet format), used by Lotus 1-2-3 , Lotus Symphony
3. IXF (integrated exchange format)

With the IXF format, most of the table attributes, as well as any existing indexes, are saved in
the IXF file, except when columns are specified in the SELECT statement. With this format, the
table can be recreated, while with the other file formats, the table must already exist before
data can be imported into it

Consider the following Example

db2 Export to myfile.del of del
Modified by chardel'' coldel; decpt,
Select * from staff

In the above example the staff table contents are exported to the myfile.del file in the current
directory. You can also change the directory by using the following

db2 Export to c:\DB2Export\myfile.del ...

While exporting the staff table into myfile.del , the strings are delimited by " , Columns are
delimited by ; and decimal point is ,

Exporting LOB Data

The LOB data can be exported to a different location by using the following syntax

db2 Export to myfile.del of del lobs to mylobs
Lobfile lobs1, lobs2 modified by lobsinfile
Select * from emp_photo

Message

The Information , Warning and Error Messages that are generated during the export are sent
to the standard Output by default. Alternatively they can be sent to a file by using the messages
parameter.

db2 Export to awards.ixf of ixf
Messages msgs.txt
Select * from staff where dept = 20


Security

One of the following authorization / Previlege is required to export data from a table.

• sysadm / dbadm
• CONTROL / SELECT privilege on each participating table or view.

Some Facts about EXPORT

• PC/IXF import should be used to move data between databases.
• The export utility produces a warning message whenever a character column with a length greater than 254 is selected for export to DEL format files.
• Data Link columns can also be exported
• Default for character Delimiter is Double Quote , Alternative is Single Quote
• Default for column Delimiter is Comma , Alternative is Semicolon

Reference

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp