Skip to Main Content

Best way to do a lookup during goldengate migration

Hi Experts,

I have a requirement where during the migration new primary keys are assigned ( and old ones are retained in a different column for reference) . Therefore I will have to do a lookup from dependent tables to the master table during the migration of dependent tables to get the new foreign key. This is a big schema and there can be a lot of dependencies like these as well as tables can be huge. As per Oracle documentation, I gathered that  I can use SQLEXEC to achieve this. I can use it in two ways (1) By creating a procedure (2) by directly executing a query.

My questions are

(1) What is the impact of using SQLEXEC on performance ? Now this stored procedure or query would get called for each row of dependent table.

(2) Is stored procedure method has some performance benefit over calling query ?

(3) If this is a performance drag, do you have any other idea to achieve the same functionality in goldengate migration ?

(4) Is there anyone who has used this and give me an idea how long some big tables took with this kind of lookup.



Post Details
Locked due to inactivity on May 31 2018
Added on May 3 2018