DB2 Version 9.7 for Linux, UNIX, and Windows

Export utility overview

The export utility extracts data using an SQL select or an XQuery statement, and places that information into a file. You can use the output file to move data for a future import or load operation or to make the data accessible for analysis.

The export utility is a relatively simple, yet flexible data movement utility. You can activate it through the Control Center, by issuing the EXPORT command in the CLP, by calling the ADMIN_CMD stored procedure, or by calling the db2Export API through a user application.

The following items are mandatory for a basic export operation:

The path and name of the operating system file in which you want to store the exported data
The format of the data in the input file
Export supports IXF, WSF, and DEL data formats for the output files.
A specification of the data that is to be exported
For the majority of export operations, you need to provide a SELECT statement that specifies the data to be retrieved for export. When exporting typed tables, you don't need to issue the SELECT statement explicitly; you only need to specify the subtable traverse order within the hierarchy

You can use the export utility with DB2 Connect™ if you need to move data in IXF format.

Additional options
There are a number of parameters that allow you to customize an export operation. File type modifiers offer many options such as allowing you to change the format of the data, date and time stamps, or code page, or have certain data types written to separate files. Using the METHOD parameters, you can specify different column names to be used for the exported data.

You can export from tables that include one or more columns with an XML data type. Use the XMLFILE, XML TO, and XMLSAVESCHEMA parameters to specify details about how those exported documents are stored.

There are a few ways to improve the export utility's performance. As the export utility is an embedded SQL application and does SQL fetches internally, optimizations that apply to SQL operations apply to the export utility as well. Consider taking advantage of large buffer pools, indexing, and sort heaps. In addition, try to minimize device contention on the output files by placing them away from the containers and log devices.

The messages file
The export utility writes error, warning, and informational messages to standard ASCII text message files. For all interfaces except the CLP, you must specify the name of these files in advance with the MESSAGES parameter. If you are using the CLP and do not specify a messages file, the export utility writes the messages to standard output.

For exporting data, you can use the task assistant available in IBM® Data Studio Version 3.1 or later. Task assistants can guide you through the process of setting options, reviewing the automatically generated commands to perform the task, and running these commands. For more details, see Administering databases with task assistants.