MySQL Forums
Forum List  »  IBM DB2

Re: IBM DB2 / MYSQL Replication
Posted by: Marcelle Johannes
Date: January 24, 2006 09:12PM

Andreas Sturm wrote:
> 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)


This is awesome technique to replicate DB2 to MySQL. Is there any other way around ? I have MySQL Tables that need to replicate to DB2 on AS/400 machine or even synchronized both Tables since we need to work that tables across platform.

Or, is there any Data Connection from AS/400 that can be acle to access MySQL Tables ??

I'll be glad if someone can provide me with this information, since i need this urgently.

Options: ReplyQuote


Subject
Views
Written By
Posted
12473
January 19, 2005 03:50AM
5793
January 19, 2005 02:14PM
5484
January 20, 2005 05:27AM
5414
January 19, 2005 04:17PM
6019
January 20, 2005 05:24AM
7854
January 20, 2005 02:49PM
7448
April 13, 2005 03:11AM
5086
April 20, 2005 08:20AM
5951
June 24, 2005 05:04PM
5021
June 24, 2005 07:44PM
Re: IBM DB2 / MYSQL Replication
12683
January 24, 2006 09:12PM
5477
January 24, 2006 09:20PM
5039
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.