Thread: Move data from db1 to db2
10-23-09, 08:20 #1Registered User
- Join Date
- Oct 2009
Unanswered: Move data from db1 to db2
We have developed a web application with a somehow complex data model (around 30 tables with several references) based on hibernate as the ORM.
This application is used by our customer on several servers. Let's say they have an application server a1 with a database db1 and another application server a2 with its own database db2. The application on a1 doesn't know that the one on a2 exists.
Let's say the application on a1 is for all users from northern europe and on a2 for those from southern europe. The application creates about 1 millions records each day.
Now the database management team realizes that the load on a1/db1 is too high whereas the load on a2/db2 is quite low. Therefore they want to switch i.e. France from the northern group to the southern group. So they have to carry the data of the french users over from db1 to db2.
(Imagine we are ebay. Then we have to move all french auction data from db1 to db2.)
As the two systems don't know each other some database identifiers are probably the same in db1 and db2 so that we cannot simply move the data from db1 to db2, but have to change the identifiers to new, not used ones.
Which strategies can we use to carry the data over? We would love to do this by pure sql, but we think that's impossible as we have to change the identifiers. Another idea is to write a tool based on our domain model and hibernate DAOs, but we don't know if that would work.
Does anybody had a similar problem and found a solution or can recommend a good approach for us?
Thanks in advance,
10-23-09, 10:28 #2Registered User
- Join Date
- Nov 2005
If you can identify data you need to move by simply writing an SQL against Db1 your problem is half solved.
Look up federated links and nicknames. Bella(db2girl) did a good write up a month of two ago on this.
In a few words. Created federated links from one db to another, create user mappings, create nicknames. Write a load script using cursor and job is done.--
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
DB2 v18.104.22.168 os 22.214.171.124