Script to copy data from one Informix database to another

Question

Hi, I have a need to copy data from one Informix database to another. I do not want to use LOAD for doing this. Is there any script that can help me with this? Is there any other way to do this?

Thanks in advance! Ravilla

Answer 1

Without a bit more information about the types of Informix databases you have, it's hard to say exactly what the best option is for you.

If it's a small number of tables and large volumes of data, have a look at onunload, onload and/or the High Performance Loader. (I'm assuming we're not talking about Standard Engine here.)

If on the other hand you have lots of tables and HPL will be too fiddly, have a look at myexport/myimport (available on the iiug.org site). These are non-locking equivalents of the standard dbexport/dbimport utilities.

Answer 2

The simplest solution is to backup the database instance and restore it to a separate instance. If this is not possible for you then there are other possibilities.

• dbexport/dbimport
• unload/load
• hand-crafted SQL inserts

If the database structure is identical then you can use dbexport/dbimport, however this will unload the data to flat files, either in the file system or on tape and then import from the flat files.

I generally find that if the DB structure is the same then load/unload is the easiest solution.

If you do not want to use load/unload dbimport/dbexport then you can use direct SQL INSERTS as follows (Untested you will need to check the syntax)

INSERT INTO dbname2@informix_server2:table
SELECT * FROM dbnam1e@informix_server1:table_name

This would of course imply consistent table structure, you could use a column list if the structure is different.

One area that will cause you issues is referential integrity. If you have foreign keys then this will cause you a problem as you will need to ensure the inserts are done in the correct order. You may also have issues with SERIAL columns and INSERTS. Load does not suffer from this problem as you can load into a table with a serial value and retain the original values.

I have often found that the best solution is as follows

• Take a schema from database1.
• Split it into 2 parts the initial segment is all table creation statements, the second parts is all of the CREATE INDEX, referential integrity etc statements.
• Create database2 from the 1st part of the schema.
• Use UNLOAD/LOAD to load the data into database2.
• Apply the second part of the schema to database2

This is very similar to the process that dbimport goes through but historically I have not been able to use dbimport as my database contains synonyms to another database and dbimport did/does not work with these.

Answer 3

UNLOAD and LOAD are the simplest way of doing it. By precluding them, you preclude the use of DB-Load and DB-Access and DB-Export and DB-Import too. These are the easiest ways to do it.

As already noted, you could consider using HPL.

You could also set up an ER system - it is harder than UNLOAD followed by LOAD, but doesn't use the verboten operations.

If the two machines are substantially identical, you could consider onunload and onload; I would not recommend it.