change data detection

rajeevm · Post by **rajeevm** » Thu Mar 22, 2007 7:18 am

Hi All,

How do I perform change data detection between the tables as we are loading the files data into the tables we want to detect the changes of new and yesterdays file . Let me explain more detail we have two tables coming which has yesterdays data and todays data How do i perform the changes . As I have asked this question earlier but then i thought it was files but we do not have access to files so we are loading the files data into the staging tables and we should perform change data detection from then on.

Try to help me out with valuable suggestions

Thanks

chulett · Post by **chulett** » Thu Mar 22, 2007 7:21 am

Table... file... doesn't really make a difference in what you need to do. So whatever advice you got in the other thread should still hold.

rajeevm · Post by **rajeevm** » Thu Mar 22, 2007 8:13 am

I can perform the change data detection by joining the tables as suggested in the earlier thread ,but I don't have any key columns in both the tables to perform the join , how could i do it

Any valuable answers

Thanks

DeepakCorning · Post by **DeepakCorning** » Thu Mar 22, 2007 10:25 am

If I understood this correctly then i think you can compare all the columns in the file with a target table Hashed file(which can be in line or populated when the load ran last time) and determine whether it is already in the target table or not.
If the number of columns are many then you can use functions like CRC etc.

rajeevm · Post by **rajeevm** » Thu Mar 22, 2007 11:00 am

Thanks Deepak,

But I am not comparing the files , but joining the tables to perform change data capture , because in the earlier thread kcblend was suggesting to join the tables and do a left join to capture the new records that are coming in ,but here i don't have any key columns in the tables can i join those two tables without any key.

I appreciate your help

DSguru2B · Post by **DSguru2B** » Thu Mar 22, 2007 11:03 am

Do a join on all the columns. Or someone has to identify keys for you to capture change.

rajeevm · Post by **rajeevm** » Mon Mar 26, 2007 2:25 pm

I have joined the two tables by using left outer join to get the new ones and my job design is

ODBC STAGE -------> TRANSFORMER----------> ODBC STAGE

In this i am joining table A(today's data) left outer join table b (yesterday's data) and putting these changes into table c but since when i am trying to run this i am getting al the 22 rows which has the two new changed records and whole previous what do i do i just want only two rows which are changes should be in the table C And for table C i have given the update action as "CLEAR THE TABLE THEN INSERT THE NEW ROWS"

Your valuable suggestions would be appreicated.

Thanks

[/img]

DeepakCorning · Post by **DeepakCorning** » Fri Mar 30, 2007 4:01 pm

Again , did you try using a hashed file as a tgt lookup and filtering out the rows which are found in the hashed file? If you do not have key columsn u can mark all of them as keys or can use something like a CRC logic.

DSguru2B · Post by **DSguru2B** » Sat Mar 31, 2007 8:25 am

While doing a join, select TableB's key a well. All new records will have null key from TableB. Filter that in the transformer by putting a contraint. Or you can also filter in the sql select.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 31, 2007 10:47 am

Start over.
Document (not in pseudo code, but in English or any other spoken language) what you have to do.
This will make the design requirements easier for you, and for us, to understand.