Merging two databases
Jim Decker needed to merge two customers' databases from the same application (Informix IDS 7.3) after one company bought the other. The schemas were identical, but both databases started their surrogate key "sids" at 1, so keys overlapped, including multiple foreign-key sids per table. Replies warned that merging usually raises wider issues (shared customers, order/PO numbering) and urged caution. No tool or shortcut was offered: the consensus was that there is no magic solution, leaving hand-written sid-renumbering conversion scripts, or simply keeping the databases separate, as the options.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I support a software application which uses Informix IDS (currently 7.3x) for all database needs. I have two customers running this app, and one customer bought the other. Now they want to combine their two individual databases into one corporate database. The databases are identical in schema, the problem is that these databases have been in use for years and the sids used as keys overlap. I have done a lot of Googling, etc, and found no easy answer. I really didn't expect to find one, but I looked. So... is there any magic? Or should I just start writing my sid conversion scripts ASAP. :) Any guidance or advice would be appreciated. Thanks, JimD
JIM DECKER said: > > So... is there any magic? Or should I just start writing my sid conversion > scripts ASAP. :) Whoa there, Nellie! What about things like shared customers, shared products? There's more to worry about than SIDs. How big are these businesses? -- Bye now, Obnoxio http://obotheclown.blogspot.com/
JIM DECKER said: > > So... is there any magic? Or should I just start writing my sid conversion > scripts ASAP. :) Whoa there, Nellie! What about things like shared customers, shared products? There's more to worry about than SIDs. How big are these businesses? -- Bye now, Obnoxio I gotta go along with Obnoxio. Great chances for issues with customer/order/po numbers. Even if you migrate over time, customers might not appreciate having a new customer_number or changes to the order_number scheme. Tread lightly.
Thanks for the response and concern Obnoxio. Fortunately. My only issue is these two databases related to my application. I don't have to worry about any other issue within the two companies. The application is stand alone and used for a very particular piece of business that both of the new partners engage in. The only idenitfying keys in the database are created by the application itself. If each company has a "John Smith", each "John Smith" can be distinguihed by the sid assigned at initial input. The problem is that we never imagined we would need to merge two databases, so we set the base sid to one for every table. So the "John Smith" from company A may have the same sid as "Suzy Anthony" from company B. Some of the tables have 5 or 6 sids in them due to foreign keys. This will be fun. :) Thanks, JimD
Hi Ralph, The app is pretty specific to one industry. It has no product numbers, no customer numbers, etc. It does not inter-relate to other databases outside of itself. The database is very well designed for its task and all important key info is given a unique sid, so the only overlap I have to worry about is in the sids themselves. My recommendation was to leave the databases seperate, simply because of the man hours I suspect will be required to combine them. However, I have to research this to make people happy. Given the responses from both yourself and Obnoxio, I think the answer is "No, Jim, there is no magic." Which is pretty much what I expected, but at least I can tell my boss I asked the experts. Thanks for the input, JimD
JIM DECKER wrote: > I can tell my boss I asked the experts. > And me.
With a name like "Obnoxio the Clown", he has to be good!!! ;) You sir, will be prominently mentioned in my report. JimD