Difference between Change Capture Stage and Difference Stage

pkothana · Post by **pkothana** » Wed Oct 22, 2003 10:39 pm

It is required that one file is compared with another one (Master File) and we have to get the Delta Records i.e. records which are either new or changed. Can anybody tell me which stage to use Change Capture or Difference and why? As I am new to Data Stage I am little bit confused. Any help in this regard is highly appreciated.
Note: Both the files will be having around 28M records.

Thanks & Regards
Pinkesh

Peytot · Post by **Peytot** » Thu Oct 23, 2003 6:30 am

The Change capture is most complet then the other stage. With the change capture you can compare 2 files New and historical (same description) and retrieve the difference between them : Insert, Update, Same or Delete (present in the second file but not in the first).
In the Output, you have a new column with an indicator and we can split the data depend on their value.
After you have the Stage Change Apply which update your historical file in comparing the historical file with the output file generated by the change capture.
The difference stage is not completed, not finished. It do an half that the change capture do and you do not have the change apply if you would like to update your historical file.

Change capture is a very good stage, you can filter your data.

Pey

Jay · Post by **Jay** » Fri Jan 28, 2005 12:24 pm

Need some help here...

I set values in the Change Capture as Delete code=2, Edit code =3, Insert code=1.

For actual inserts into the target tbl, what is happening is , change_code=2.

I am comparing two tables. I think waht is happening is for inserts, there are no rows in the 2nd table. So delete code = 2 is correct.

Any thoughts?

Thanks
Jay

T42 · Post by **T42** » Tue Feb 01, 2005 1:18 pm

Your links are backward.

Click on "Link Ordering" under "Stage" tab, and ensure that your Before and After links correctly identify the input links you are using for this stage.

Jay · Post by **Jay** » Tue Feb 01, 2005 4:15 pm

T42,

Thanks for the idea...now its working good...

one more question....Edit code=3 is never set in my case....

I am changing the value of one of the columns....so for the same key it should give a change_code of 3.....

but it is giving a change_code of 2.

Any suggestions?

Thanks
jay

T42 · Post by **T42** » Tue Feb 01, 2005 8:10 pm

Edit means that your value fields have been changed for the same key.

Changing your key make a mismatch, and thus, you have BOTH a new and a delete.

Jay · Post by **Jay** » Wed Feb 02, 2005 2:45 pm

Thanks, T42. It seems that the job is working fine.

I'll keep you posted...