About this task
Figure 1. Import/Export through DB2 Connect
The DB2® export and
import utilities allow you to move data from an IBM® mainframe 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 an IBM mainframe server database into
a PC/IXF file, and then import it into a DB2 for Linux, UNIX, and 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™, System z®, IBM Power Systems™,
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:
Before you begin
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).
Procedure
- Export the data from the host server database table to
a PC/IXF file.
- Import the PC/IXF file into a DB2 table.
Example
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.
What to do next
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.