I got a scenario that cause me headache.
Currently our company is using an AS/400 Server which run DB2 to hold all critical data(I called it remote site). I dont' have acess to this database. My local site use SQL Server. SQL Server uses DTS to transfer data from DB2 to its local database.
Because of design issue, DB2 has 2 tables to contain the item information (like name, brand, categories), and SQL Server only has one. To keep the data most updated(DTS only runs twice a day), whenever a person add an item in DB2, he/she has to inform people in local site to manually add an item to SQL Server, too. Now i want to automate this process, or at least make it more trackable, because the person who add in DB2 might forget to inform the person to add in SQL Server.
I came up with two solutions:
1. Create trigger in DB2, then whenever a new item is added, DB2 will send an email message to the person who are responsible to add items to SQL Server with relevant information.
2. Design a new DTS package in SQL Server, keep track of the change of 2 tables in DB2.
For the first solution, I'm not familiar with DB2, and I posted my questions in DB2 forum here. But there is no response yet. Also, it would be costly since i don't have access to DB2 and need to pay someone else to do this for me. For the second solution, the DTS has to be run quite frequently (which i think may not be a good way) since i don't know when the person in remote site will add item in DB2, and I really need this info most recent updated.
Could anyone tell me which solution i should go or is there any better solution? Sorry for the long post, i'm looking forward to any of your suggestions.
BenGeneral thoughts:
You'll still have to choose between time and money :).
I mean that you cannot get immediate notification about the changes on remote site unless it will inform you in some way, so if you want to be informed as fast as possible - you'll have to alter remote site. Less reaction time, more money.
OR you may setup a SQL Server job that will on periodic basis check the remote site for changes|||try using MS SQL Server - snapshot replication
No comments:
Post a Comment