• No replies
1 Post

Pinned topic Can Optim support the following migration scenario?

‏2013-05-31T08:27:35Z | business key optim surrogate


Could you please help me to understand if Optim can support the following migration scenario. Thanks a lot.

For example, I have a table Lane(lane_id, origin_id, dest_id), where lane_id is PK and it grows by DB sequence (i.e. surrogate key), and ori_id+dest_id is unique key on Lane (i.e. business key). And this lane table has a child table lane_detail (ld_id, lane_id, desc), where ld_id is pk, and lane_id is a foreign key to lane.

Lane                            Lane_detail
lane_id   PK   <------>  lane_id   FK  
ori_id      UK                ld_id     PK
dest_id   UK                desc

I have already archived the lane and lane_detail to a archive file, and plan to insert this archive file to a target DB (where lane and lane_detail already exist) with the following requirements.

When inserting the lane row to the target DB:
(1) First query the lane table in target DB by ori_id and dest_id, if a row is found, then use that row's lane_id when inserting the lane row in target DB, if no row is found then we just insert lane as usual.
(2) If a row is found in (1), then update the lane_id in child table lane_detail before inserting this table.

In simple, I want to migrate tables to target DB and during migration keep the surrogate keys in target DB if existed.

So I am looking at following 2 map column functions, which seems if I combine to use them in column mapping, can help for the above scenario.
1) Propagate function, this function assigns a value to a primary key or foreign key column and propagates that value to all related tables. It seems can help in (2) above

2) Lookup function, this function obtains the value for a destination column from a lookup table, according to the value in a source column.

So here is the expression that I attempted to try in column mapping editor for the source lane_id column.


I want you to help me understand if

1) The combination use of these 2 functions can help in my scenario, or do you have other suggestion?
2) By looking thru the LOOKUP function I didn't find any parameters that can help support "if no row is found in target lane, then we still use the original lane_id in source lane". Could you advise it?

Thanks for your help!