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.