Data-migration tools

Informix® provides tools, utilities, and SQL statements that you can use to move data from one HCL 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
Nonlogging raw tables

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

The dbimport and dbload utilities
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
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 Choosing among dbimport, dbload, and LOAD will help you make a decision.

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. — Figure 1. Choosing among dbimport, dbload, and LOAD

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.