MySQL Forums
Forum List  »  IBM DB2

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


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 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 :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

Written By
January 19, 2005 03:50AM
January 19, 2005 02:14PM
January 20, 2005 05:27AM
January 19, 2005 04:17PM
January 20, 2005 05:24AM
January 20, 2005 02:49PM
April 13, 2005 03:11AM
April 20, 2005 08:20AM
Re: IBM DB2 / MYSQL Replication
June 24, 2005 05:04PM
June 24, 2005 07:44PM
January 24, 2006 09:12PM
January 24, 2006 09:20PM
August 02, 2005 06:45AM

Sorry, you can't reply to this topic. It has been closed.

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.