Data-migration tools

Informix® provides tools, utilities, and SQL statements that you can use to move data from one IBM® Informix database to another or from one operating system to another.

You might want to use a data-migration tool when you have different page sizes or code pages. For example, UNIX or Linux and Windows store data in different page sizes.

When your migration involves migrating between different operating systems, you must export data and its schema information from one database server and import the exported data into the other database server.

Normally, if you are migrating on the same operating system, you do not need to load and unload data.

You can use the following tools to move data:

  • The dbexport and dbimport utilities
  • The dbload utility
  • The onunload and onload utilities
  • UNLOAD and LOAD statements
  • The High-Performance Loader (HPL)
  • Nonlogging raw tables

When you import data from non-Informix sources, you can use the following tools:

  • The dbimport and dbload utilities
  • The High-Performance Loader (HPL)
  • IBM Informix Enterprise Gateway products
  • External tables that you create with the CREATE EXTERNAL TABLE statement

The best method for moving data depends on your operating system and whether you want to move an entire database, selected tables, or selected columns from a table. The following table summarizes the characteristics of the methods for loading data and the advantages and disadvantages of each method. The table also shows the database servers on which you can use the tools.

Note: dbimport cannot be used to move datablade data between Informix versions.
Table 1. Comparison of tools for moving data
Tool Description Advantages Disadvantages
dbexport and dbimport utility

Imports or exports a database to a text file that is stored on disk or tape

Can modify the database schema and change the data format

Can move data between operating systems

Optional logging

Can import data from non-Informix sources

Faster performance than the dbload utility, but slower performance than the onload utility

Moves the entire database

dbload utility

Transfers data from one or more text files into one or more existing tables

Can modify database schema

Can move data between operating systems

Optional logging

Moderately easy to use

Can import data from non-Informix sources

Slower performance than the dbexport, dbimport, and onload utilities
onunload and onload utilities

Unloads data from a database into a file on tape or disk; loads data, which was created with the onunload command, into the database server

Fast performance Optional logging

Only moves data between database servers of the same version on the same operating system

Cannot modify the database schema

Logging must be turned off

Difficult to use

UNLOAD and LOAD statements

Unloads and loads specified rows

Can modify database schema

Can move data between operating systems

Easy to use

Optional logging

Only accepts specified data formats

HPL

Loads data from any ASCII or COBOL file that meets certain format requirements

For extremely large databases, has a performance advantage over other IBM Informix data-migration utilities, because it performs I/O and code-set conversions in parallel

Can modify database schema

Can move data between operating systems

Can import data from non-Informix sources

Requires significant preparation time

Nonlogging raw tables Loads certain kinds of large tables Can load very large data warehousing tables quickly

Does not support primary constraints, unique constraints, and rollback

Requires SQL

Not recommended for use within a transaction

External tables Enables you to read and write from a source that is external to the database server, providing an SQL interface to data in text files managed by the operating system or to data from a FIFO device. Performs express (high-speed) and deluxe (data-checking) transfers

Requires SQL

If you are choosing a tool for loading data, the questions shown in Figure 1 will help you make a decision.
Figure 1. Choosing among dbimport, dbload, and LOAD
Use dbimport to move an entire database, dbload to skip some rows or commit inserts, or a LOAD statement if the data format is acceptable.

In addition to the tools that move data, you can use the dbschema utility, which gets the schema of a database and redirects the output to a file, so you can provide the file to DB–Access to re-create the database.