Informix Specific Table export

exportimportinformixmigration

I'm busy trying to migrate data between informix databases using the dbexport and dbimport utilities.

I know that in PostgreSQL I can use the following

pg_dump –a –t table database > /destination/table.sql

Which I can then import using

psql database < /destination/table.sql

Is there an equivalent for the informix dbexport and import utilities?

Best Answer

The dbimport tool assumes that it must create the database (and won't work with an existing database). There isn't a utility that migrates data like you're trying to do.

You have multiple options. One is to use one of the replication schemes (ER, HDR, RSS, etc) to create a clone of the original database. Another is to create the relevant SQL yourself and run that via DB-Access or another tool. You might find external tables or HPL relevant for getting good performance on the loading.

What do you do with the data that already exists in the target database? Delete it before importing the new? Save it in case the new doesn't import cleanly? Do you have constraints — primary key, foreign key — that will complicate any data loading? Do you need to defer those constraints while all the loading takes place? Do you want to rebuild the tables from scratch?

What size of database is under discussion? Megabytes, gigabytes, terabytes, petabytes? How many tables? How big are the biggest tables — row size, number of columns, number of rows?

Incidentally, if the PostgreSQL tools simply export the data from one table and import into another, then that can be done with my SQLCMD program — a program which pre-dates Microsoft's johnny-come-lately program of the same name by over a decade. SQLCMD has two aliases, sqlunload and sqlreload. You could use:

sqlunload -d old_database -t old_table |
sqlreload -d new_database -t new_table

You can save the data in a file, of course:

sqlunload -d old_database -t old_table -o old_table.unl
sqlreload -d new_database -t new_table -i old_table.unl

And there can be file transfers, etc, as required in between the unload and reload operations. If you need to truncate the new table before loading, that's a separate operation:

sqlcmd -d new_database -e 'truncate table new_table'

And so on. By default, sqlreload (you can also use sqlcmd -R … for that, and sqlcmd -U … for sqlunload) will batch the loading into sub-transactions of 1024 rows, but you can configure that, of course.

Related Question