Synchronizing tables between AS/400 and MS SQL 2000
Posted on 2004-10-06
Here's my dilemma. I have a live production database running on an AS/400. We use an MS SQL 2000 database as a warehouse for that same data so that we can run our reports off of the SQL2000 instead of taxing our aging AS/400. We probably tax the AS/400 more the way we do it than if we could somehow just run our reports straight from the horse's mouth.
Our vendor has a solution that uses triggers to automatically sync each transaction from the AS/400 to the MSSQL server so that corresponding tables on both servers are always in sync. It doesn't work anymore for reasons I won't get into. Their secondary solution (the overtaxing one) is to use DTS packages to copy the tables from AS/400 to SQL.
The tables currently work as such: Drop table from SQL database, create new table, copy all data from corresponding AS/400 table.
I would love a way to synchronize rather than drop and recreate. We're talking about 1.5 million rows twice a day here. I have it all scheduled so it's pretty hands off...but the data is never realtime in this method. Our vendor's solution requires lots of triggers to be placed on the AS/400 and a client application to run on the SQL server. We no longer have the AS/400 expertise to do this kind of AS/400 database stuff.
Appending tables won't work, because rows are not just added, the are also changed, and not always in sequence...so if you just added the new rows you might miss changes made to previous stuff. Is there a way to compare the two tables and only copy data from changed entries? I can't think of one and I don't think it's possible without the AS/400 triggers...but you people are the experts so I thought I'd pose my question to you.
Sorry this got so wordy...I just don't like having to go back and clarify things a lot.