Moving data with DB2 Connect


If you are working in a complex environment in which you need to move data between a host database system and a workstation, you can use DB2 Connect™, the gateway for data transfer between the host and the workstation (see Figure 1). Figure 1. Import/Export through DB2 Connect

This diagram illustrates the movement of data between a host database system and a DB2 client using DB2 Connect.

The DB2® export and import utilities allow you to move data from a host or System i™ server database to a file on the DB2 Connect workstation, and the reverse. You can then use the data with any other application or relational database management system that supports this export or import format. For example, you can export data from a host or System i server database into a PC/IXF file, and then import it into a DB2 for Windows® database.

You can perform export and import operations from a database client or from the DB2 Connect workstation.


  1. The data to be exported or imported must comply with the size and data type restrictions that are applicable to both databases.

  2. To improve import performance, you can use compound queries. Specify the compound file type modifier in the import utility to group a specified number of query statements into a block. This can reduce network overhead and improve response time.

With DB2 Connect, export and import operations must meet the following conditions:

If any of these conditions is not met, the operation fails, and an error message is returned.

Note: Index definitions are not stored on export or used on import. If you export or import mixed data (columns containing both single-byte and double-byte data), consider the following:


Moving Data from a workstation to a host server

To move data to a host or System i server database:

  1. Export the data from a DB2 table to a PC/IXF file

  2. Using the INSERT option, import the PC/IXF file into a compatible table in the host server database.

To move data from a host server database to a workstation:

  1. Export the data from the host server database table to a PC/IXF file.

  2. Import the PC/IXF file into a DB2 table.



The following example illustrates how to move data from a workstation to a host or System i server database.

Export the data into an external IXF format by issuing the following command:

   db2 export to staff.ixf of ixf select * from userid.staff 

Issue the following command to establish a DRDA® connection to the target DB2 database:

   db2 connect to cbc664 user admin using xxx 

If it doesn't already exit, create the target table on the target DB2 database instance:

          COMM DECIMAL(7,2)) 

To import the data issue the following command:

   db2 import from staff.ixf of ixf insert into mydb.staff 

Each row of data will be read from the file in IXF format, and an SQL INSERT statement will be issued to insert the row into table mydb.staff. Single rows will continue to be inserted until all of the data has been moved to the target table.

Detailed information is available in "Moving Data Across the DB2 Family," an IBM® Redbooks™ publication. This Redbooks publication can be found at the following URL:

Related concepts
DB2 Connect

Related reference
db2look - DB2 statistics and DDL extraction tool command PC Version of IXF file format EXPORT command IMPORT Command