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)