19
Hi, Folks.
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.
Ben
