[Nottingham] Merging databases problem

Martin martin at ml1.co.uk
Sat Nov 19 00:34:19 GMT 2005

David Aldred wrote:
> On Friday 18 Nov 2005 17:43, Martin wrote:
>>"The two databases are physically identical, just contain different
>>data. The issue is that several fields are of user-defined type, which
>>apparently don't just get copied with a SQL "insert into db1:table
>>(x,y,z) select (x,y,z) from db2:table". Some really annoyingly long SQL
>>may do the trick, except that these types contains lists of indefinite
>>length, and from what we've experienced, if you screw up the transfer,
>>you hose the *enter* table."
> Immediate thought: what's the field type going to be in the merged table?   If 
> you know what field type you're aiming at, can't you programatically cast the 
> existing data into the new format? 

They have C++ wrappers for doing various tricks for inserting new 
data... but:

"We actually already have the C++ tools to insert/update, etc. (since 
that's how the data got in there in the first place). The hard part is 
getting the join to work (preserving relational constraints as we move 
joined tables from one db to another, losing original IDs in the 
process), and we're running into memory issues (keeping maps of old 
ids/new ids in memory). As well, we need to work in some parallelism to 
shrink the amount of time this all takes."

This is all for a few billion entries and on old Sun Solaris kit.

Any ideas to avoid a month or so of C++ hackings?

And I don't want to learn databases at this instant!


Martin Lomas
martin at ml1.co.uk

More information about the Nottingham mailing list