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.
| 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 |
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.