Data Movement Utilities Guide and Reference

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 from the host to the workstation, as well as the reverse (see Figure 9).

Figure 9. Import/Export through DB2 Connect

The following section discusses exporting and importing data using DB2 Connect.

Using the Export and the Import Utilities

The DB2 export and import utilities allow you to move data from a DRDA 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 DB2 for MVS/ESA into a delimited ASCII file, and then import it into a DB2 for OS/2 database.

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

Notes:

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 SQL. Specify the compound file type modifier in the import utility to group a specified number of SQL statements into a block (see File Type Modifiers (Import)). This may reduce network overhead and improve response time.

3. For detailed information about the syntax of the export and the import utilities, see EXPORT Command, and IMPORT Command.

Moving Data from a Workstation to a DRDA Server

To move data to a DRDA 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 DRDA server database.

Moving Data from a DRDA Server to a Workstation

To move data from a DRDA server database:

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

Restrictions

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

• The file type must be PC/IXF.
• A table with attributes that are compatible with the data must exist before you can import to it. Import through DB2 Connect cannot create a table, because INSERT is the only supported option.
• A commit count interval must not be specified for the import operation.

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.

Mixed Single-Byte and Double-Byte Data

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.