DB2 Connect User's Guide

Using Import and Export Utilities

The import and export utilities let you move data from a host or AS/400 database to a file on the DB2 Connect workstation or vice versa. You can then use this data with any other application or RDBMS that supports this import/export format. For example, you can export data from DB2 for MVS/ESA into a delimited ASCII file and later import it into a DB2 for OS/2 database.

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

Notes:

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

2. To improve import performance, you can use compound SQL. Specify COMPOUND=number in the import API or the CLP filetype-mod string parameter to group the specified number of SQL statements into a block. This may reduce network overhead and improve response time.

3. For information on the syntax of the import and export utilities from the command line processor, see the Command Reference manual.

Moving Data from a Workstation to a Host or AS/400 Database Server

To export to a host or AS/400 database server:

1. Export the rows of information from the DB2 table into a PC/IXF file.

2. If the host or AS/400 database server database does not contain a table having attributes compatible with the information to be imported into it, create a compatible table.

3. Using the IMPORT command with the INSERT option, import the PC/IXF file to a table in the DRDA server database.

Moving Data from a host or AS/400 database server to a Workstation

To import data from a host or AS/400 database server:

1. Export the rows of information from the server database table to a PC/IXF file.

2. Use the PC/IXF file for importing to a DB2 table.

Restrictions

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

• The file type must be PC/IXF.

• Index definitions are not stored on export or used on import.

• A table with attributes that are compatible with those of the data must exist before you can import to it. Importing through the DB2 Connect program cannot create a table because INSERT is the only supported option.

• A commit count interval must not be specified with import.

If these conditions are violated, the operation will fail and an error message will be generated.

Mixed Single-Byte and Double-Byte Data

If you import and export 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 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 data in a column has a consistent pattern. If it does, fixed length is acceptable.

Replacement for SQLQMF Utility

The function of the SQLQMF utility with DDCS for OS/2 has been replaced by the DB2 Connect Import/Export functions. The advantages are:

• No need for QMF on the host

• No need to logon to the host (a TSO id is still required on DB2 for MVS/ESA or DB2 Universal Database for OS/390)

• Supports DB2 for MVS, DB2 for OS/390, DB2 for OS/400, and DB2 for VM and VSE

• Good performance achieved by using compound SQL

• Supports several file formats, in addition to ASCII

• Can be run from a client machine with no SNA connectivity.

Refer to the Command Reference for further information on using these commands.

[ DB2 List of Books | Search the DB2 Books ]