MySQL Forums
Forum List  »  IBM DB2

Re: IBM DB2 / MYSQL Replication
Posted by: Andreas Sturm
Date: June 24, 2005 05:04PM

Hi,

first we first have tried this solution (if you like the fast one scroll down !):
**************************************************************
We used Java on the AS400 System, opened a connection to the DB2 via JDBC then opened a connection to MySQL via JDBC (all this on the AS400, used IP connection), used a query, got a recordset on one of the databases (for example DB2) and wrote the data (fetched each record) into the other database (for example into MySQL). DELETE and INSERT is only one Query, an UPDATE was made in two steps:

1. DELETE all records with the same key in the target database (for example WHERE ARTNO = 4711)
2. INSERT the current (new) record into the target database

D. Bender http://www.bender-dv.de helped us with that Java program, you can get a freeware on his website.

This solution worked fine by using our LAN (100BaseT), but we have to synchronize the MySQL systems at our stores, using one ISDN channel in each case, and every night we have transmitted about 10.000 up to 20.000 records (max. 280.000 recs). We've got 14 locations (take a look at www.handelshof.de :o)) and the timeframe to replicate was not sufficing. This solution is very safe, and easy to use but very, very, very, very slooooowww.

***************************************************************
2. attempt

Disappointed about the bad performance we have developed our own DataReplicator. Now we create an ascii file for each update process (1 - 280.000 records at one time) on the AS400, including only the data, not a SQL Statement, then the ascii file will be packed by the AS400, after all we're using a mapped drive on a fileserver in each location to copy that packed ascii file into the location. We've developed a software running on a second machine at every location for decompressing the archive and writing the records into the MySQL Database.

This solution is very, very fast, very safe but not easy to use. You have to establish a lot of logical rules to assure that the data will be replicated in the correct order. But it is working fine.

1. advantage

There is no "SQL overhead" while copying the data, you've got only data in that file.

2. advantage

All the data is compressed.

3. advantage

There is no communication overhead, because a single update process (1 - 280.000 records) creates only one file to copy. By using the first solution, 280.000 records means: 280.000 single SQL Statements (if it is an DELETE or INSERT, UPDATE needs two statements) and each statement will be answered, that way you've got minimum 560.000 "messages running over the cord" ;o)

4. advantage

time, time, time, time

The first solution had run min. 5 hours to replicate 280.000 records.

Our own Datareplicator is runinng 10 min. to replicate 280.000 records. (incl. filecopy)

Options: ReplyQuote


Subject
Views
Written By
Posted
12502
January 19, 2005 03:50AM
5808
January 19, 2005 02:14PM
5496
January 20, 2005 05:27AM
5448
January 19, 2005 04:17PM
6031
January 20, 2005 05:24AM
7890
January 20, 2005 02:49PM
7458
April 13, 2005 03:11AM
5098
April 20, 2005 08:20AM
Re: IBM DB2 / MYSQL Replication
5963
June 24, 2005 05:04PM
5026
June 24, 2005 07:44PM
12713
January 24, 2006 09:12PM
5498
January 24, 2006 09:20PM
5053
August 02, 2005 06:45AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.