Data migration between different DBMS's

As i couldnt get any satisfying answer to my Question it seems we have to write our own program for that, we are in the design phase and we are thinking which format shall we use to backup the data.

The program will be written in Delphi.

Needed is Exporting/Importing data between Oracle/Informix/Msserver, very important here is the Performance issue, as this program will run on a 1-2 GB Databases. Beside the normal data there are Blobs in the Database which have to be backuped.

We thought of Xml-Data or comma-separated data as both are transparent (which is nice to hav开发者_StackOverflowe), but Blobs must be considered here. Paradox format is not optinal in this case.

Can anybody recommend some performant formats?

Any other Ideas to achieve the same Goal are welcome.

Thanx in Advance.

I use an excellent program called OmegaSync for my backups, but it will only handle Informix via ODBC and not directly. If you find you can use OmegaSync, you'll find its performance to be excellent, because it compares the databases first, and then syncs only the differences. You might want to use this idea if you decide to do the programming yourself if efficiency is your number one goal.

But programming database conversion is very complex as others answers to your question have said. So why not just develop the SQL you need, and do the conversion that way. For example see: Convert Informix Schema to Oracle Schema Or Any Other RDBMS For moving the data, check out sources like: Moving non-informaix data between computers and dbspaces

You can optimize the SQL to what I'm sure will be an adequate speed if you dump and load your data smartly.

DbUnit is a popular tool which can extract and load data in XML format, see

http://www.dbunit.org/faq.html#extract

    // partial database export
    QueryDataSet partialDataSet = new QueryDataSet(connection);
    partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'");
    partialDataSet.addTable("BAR");
    FlatXmlDataSet.write(partialDataSet, new FileOutputStream("partial.xml"));

    // full database export
    IDataSet fullDataSet = connection.createDataSet();
    FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));

Did you check ODI (Oracle Data Integrator) It has support for lots of source databases. It is able to capture changes from the source databases and integrate them in the target database. It is performant but has a price tag.

Ronald.

The new DBExpress framework give the possibility to exporting/importing data between many databases. you can check this CodeRage session Deep Dive into dbExpress by John Kaster

You should use your own binary format, integrated by (xml for text/streams for Blobs).

If you have to export metadata too and not only data, it could be very complex. There are many subtle (and not so subtle differences) among the databases you're going to use, that such a format should be general enough and the exporting/importing code should be able to translate and map metadata across databases, and because an external application can't write directly to the database internal structures, it would have to generate the db proper DDL to create the data structures. As long as this is a proprietary format, IMHO its design is the least of your issues, if size and performance are important and the file is read sequentially it would not be difficult to design a binary format. Anyway import/exports and backups are two different tasks. If you have to backup a database, use its facilities. They usually allow far more control, i.e. point-in-time recovery. If you have to move data across databases that's another issue - I would write just the code to move data, not metadata, pre-creating the required structure in the target database.

You could give Toad (Quest Software) a try.

It supports all your mentioned platforms and can do things like 'Export table data to INSERT statements' on your source platform which can then be run on the target platform. IIRC there is even some Toad-internal backup-format which might be cross-platform.

Toad Communities:

• Toad for ORACLE
• Toad for SQL SERVER
• Toad for OTHER RDMBS (including Informix)

Some videos about exporting, importing:

• YouTube: Toad for Data Analysts v2.7 Export Enhancements
• YouTube: Toad for Data Analysts v2.7 Import Enhancements