I am writing a database replicator software to replicate new records from a source DB2 database to a target MySQL database. However, the replication seems to be rather slow, cuz the software basically grabs records from the DB2 database 100,000 at a time and does comparison of the primary keys to see if they exist in the target table.
I would like to ask if anyone has a better method for doing this job? I was actually thinking of running a small daemon program on the source machine which detects if new records have been added and then zaps these records over to the target machine but the source machine seems a little overloaded at the moment.
You could create triggers on the DB2 tables which would log inserts, updates, and deletes to another DB2 table (with an extra column to indicate insert, update, or delete). Then you would have a table of just the items that changed on the table. Obviously you would want to delete the rows off the DB2 log table as you posted them to MySQL.