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.

Note:

The data to be exported or imported must comply with the size and data type restrictions that are applicable to both databases.
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:

The file type must be PC/IXF.
A target table with attributes that are compatible with the data must be created on the target server before you can import to it. The db2look utility can be used to get the attributes of the source table. Import through DB2 Connect cannot create a table, because INSERT is the only supported option.

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:

On systems that store data in EBCDIC (MVS™, OS/390^®, OS/400^®, VM, and VSE), shift-out and shift-in characters mark the start and the end of double-byte data. When you define column lengths for your database tables, be sure to allow enough room for these characters.
Variable-length character columns are recommended, unless the column data has a consistent pattern.

Moving Data from a workstation to a host server

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

Export the data from a DB2 table to a PC/IXF file
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:

Export the data from the host server database table to a PC/IXF file.
Import the PC/IXF file into a DB2 table.

Example

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:

   CREATE TABLE mydb.staff (ID SMALLINT NOT NULL, NAME VARCHAR(9), 
          DEPT SMALLINT, JOB CHAR(5), YEARS SMALLINT, SALARY DECIMAL(7,2), 
          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: http://www.redbooks.ibm.com/redbooks/SG246905.

Related concepts
DB2 Connect