DB2 Version 9.7 for Linux, UNIX, and Windows

Exporting data

Use the export utility to export data from a database to a file. The file can have one of several external file formats. You can specify the data to be exported by supplying an SQL SELECT statement or by providing hierarchical information for typed tables.

Before you begin

You need DATAACCESS authority, the CONTROL privilege, or the SELECT privilege on each participating table or view to export data from a database

Before running the export utility, you must be connected (or be able to implicitly connect) to the database from which you will export the data. If implicit connect is enabled, a connection to the default database is established. Utility access to Linux, UNIX, or Windows database servers from Linux, UNIX, or Windows clients must be through a direct connection through the engine and not through a DB2 Connect™ gateway or loop back environment.

Because the utility issues a COMMIT statement, you should complete all transactions and release all locks by issuing a COMMIT or a ROLLBACK statement before running the export utility. There is no requirement for applications accessing the table and using separate connections to disconnect.

You cannot export tables with structured type columns.

Procedure

To run the export utility:

Specify the EXPORT command in the command line processor (CLP).
Call the db2Export application programming interface (API).
Open the task assistant in IBM® Data Studio for the EXPORT command.

Example

Issuing an EXPORT command by using the CLP

A very simple export operation requires you to specify only a target file, a file format, and a source file for the SELECT statement.

To export data from the CLP, enter the EXPORT command:

db2 export to filename of ixf select * from table

where filename is the name of the output file that you want to create and export, ixf is the file format, and table is the name of the table that contains the data you want to copy.

However, you might also want to specify a messages file to which warning and error messages will be written. To do that, add the MESSAGES parameter and a message file name (in this case, msg.txt) so the command is:

db2 export to filename of ixf messages msgs.txt select * from table

For complete syntax and usage information, see "EXPORT command."