Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This question is about databases but I prefer to post it here and not in dba.stackexchange. The reason is that I have concerns with the application as well as database design. Programmers can better assist in this regard.

My four year old .NET application is using MySQL 5.1 database server as back-end. The database is running well. There are no performance issues to make us worry about. But I want to try out advanced database features like Partitioning, Replication and Clusters. I have never used these concepts before and I can definitely give these features a try on existing database, but I have some other plans.

I want to use IBM DB2 express-C as an experimental database. Records from MySQL need to be synchronized into DB2 database and I want to use the DB2 database for reports in my application.

Is there any free tool to synchronize the two databases? Do I need to use two connection strings, the first one involving MySQL for Inserts/Updates/Deletes and the second one involving DB2 for Reporting?

Is this type of foolish approach used in real world in certain scenarios?

share|improve this question
    
Out of curiosity...why DB2? –  John Straka Jul 26 '11 at 16:16
    
Using a separate database for reporting is fairly common. Usually, however, you either use another one nearly identical to the origin of the data, or else some sort of data warehousing kind of thing that's specifically designed to harvest data from other sources. –  Jerry Coffin Jul 26 '11 at 16:21
    
@John: It has a rich GUI Admin tool and an earnest desire to use try it. –  RPK Jul 26 '11 at 16:28
    
@Jerry: You discussed about Warehouse. Is there any way to just move out rows of old years that are not needed from the production database to another database? I want to create a data dump which can be queried when needed and at the same time reducing the size of the current production database. –  RPK Jul 26 '11 at 16:32
    
@RPK Are you only interested in the GUI Admin tool? MySQL also provides that in the form of the MySQL Workbench. Do you have any technical reasons for moving dbs? –  Thomas Owens Jul 26 '11 at 16:55

2 Answers 2

up vote 1 down vote accepted

This is a common scenario in the database world. Nice to see someone who wants to learn it. Companies send data back and forth to different systems all the time (both their systems and their clients systems and their vendors systems). I can't speak to free tools to do this because I live in SQL Server world and SSIS is the tool we use which comes with SQL Server. But what you want to search for is ETL tools. Tools for this are out there, I just don't know which ones are the best. ETL stands for Extract, Transform, Load which are the steps in the process of moving data from one database to another.

Let me describe some of the issues you need to think about when sending data back and forth between two systems. The first and most important is that it is highly unlikely that the two databases will be simliar in structure. Fields that are reuired in one may not exist in the other. Data types will be different. Business rules will be different. Lookup values will be different.

You will have to handle things like the States table which will exist in one db and all addresses must use a valid 2 digit US state code in the required State field, but the data is coming from a table that allows any value (or even no value) to be put in a state field in the address table, therefore it has things like Virginia, Ohoi (instead of Ohio), CA, ON (Canadian state fo Ontario), and Unknown as well as null values. You have to figure out waht to do with records that have data that won't fit in the receiving database.

You need to think about how will you match up existing records to the ones already in your database.

Another thing you have to think about is how to handle data that is merged by one database due to finding and fixing duplicates. Do you need to have way to make sure it gets merged in the other database?

You have to think of performance. This can be an intense process that takes a lot of the resources of both databases. You need to think in terms of how can I minmize the impact to other users of the database.

Most ETL tools will let you send directly to the other database (generally if they are in the same network) and other times you need to create one or more files to send to a clinet in another location. Then you need to think about what the structure of the data sent should be. Should you have one denormalized file or several files that refelect the way your own database is normalized. Should the sender clean the data to the receiovers specs or vice versa or should both be doing some scrubbing. Are there legal requirements (think about sending patient data from a docotprs office to a hospital for instance).

There's a lot more, ETL is a complex specialty, but I think that is enough to get you started on thinking about the planning you have to do once you find your tool.

share|improve this answer
    
DB2 has a migration toolkit to import MySQL Schema and data into it. It automatically creates the schema so there is no worry to match data types. –  RPK Jul 26 '11 at 17:17

Well, you not only need two different connection strings, you also need completely different database connectors. And then the fun starts for real.

Most likely DB2 uses a different SQL syntax, so you would need to check all your select queries whether they actually produce the same output. Plus: each database manufacturer has a few custom features built into their dialect of SQL. Things that work under MySQL are not automatically guaranteed to work with DB2. Which means that just transferring or replicating the data into the DB2 database could be a major job on its own.

I have not come across this approach in any real world scenarios, but then, I don't know every real world scenario that exists out there.

From experience I would advise to steer well clear of this sort of thing unless you have a really strong reason to go down that road. You are most likely in for a world of pain for very little gain.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.