1

Site: Oracle Exadata as Source and Netezza as Target

Scenario: Records of Table A are being replicated to the Target, during replication, these records are to be tagged with a specific date (a.k.a rundate) from Table B.

Table A (contains many records) Col1, Col2, Col3... Rundate

Table B (contains only 1 record at any point of time) Rundate, ColA, ColB...ColZ

There is no common key available between these tables. I tried using the %GETCOL function to retrieve the column and running into issues.

How can I achieve this? What would be the performance impact brought by the solutions?

Sarah Orvis
  • 71
  • 6

1 Answers1

0

The way to implement this would be via a user exit in a source derived column. The user exit can be either in Java or a stored procedure in PL/SQL The user exit would simply do something like select rundate from tableB where rownum=1 , and then pass that back to CDC via an output parameter The expression to populate the source derived column would be %USERFUNC

As for the impact on performance of subscriptions, it would really depend on what the stored procedure had to do and how efficiently it was written, For example if it was written or implemented n such a way that the data and the code were retained in memory after the first call after the start of replication the impact would be smaller than if disk reads were required for every invocation,. By the way using %GETCOL is not without some cost as it implies a read into the database for every row operation processed. Generally, Java is considered to be more efficient than a stored procedure.

Sarah Orvis
  • 71
  • 6