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 want to 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, 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.

Note: EXPORT utility is an embedded SQL application, and does not support dynamic data format.

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

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

For example:
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 are written. To do that, add the MESSAGES parameter and a message file name (in this case, msg.txt). For example:
db2 export to filename of ixf messages msgs.txt select * from table