If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Move data from db1 to db2

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 24
Move data from db1 to db2

Hi!

The situation:

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.


The problem:

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.


The question:

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,
Ole
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2005
Location: IL
Posts: 557
Ole,

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 v9.7.0.6 os 6.1.0.0
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On