Change Data Capture

[vick]

Hello,

I have 2 sequential files with 6000 & 6500 records respectively. I need to do an upsert into Oracle Table. I have to capture the changes in the input files and set the flag namely Unchanged, Insert, Update or Delete.

This is what I m doing presently.

The input flat files are previous day & current day files and has 44 Columns. The Oracle Table has 45 columns (44 same as the input) with the operation_flag as the 45th. I want to populate Operation_Flag column with the Unchanged, Insert, Update, Delete Code (0,1,2,3).

Code: Select all

  SeqFile1
        |
       CDC-Stage--------------------->Oracle_Ent_Stage
        |
  SeqFile2

Pl suggest

TIA

[DSguru2B]

The CDC stage gives an extra column with the codes. You must be getting them, so whats the problem here. Just insert them. Or you want wordings instead of codes? If thats the case then stick in a transformer and change the codes to words.

[vick]

I m not getting the codes for DELETE n UPDATE.

I m getting '0' and '1' for Unchanged & Insert.

There are records in previous day file that does not exist in the current day file (which means its a delete but I dont get a code for that).

Any pointers

[samsuf2002]

SET 'Drop output for Delete' = false in CDC so that u can capture deletes

[vick]

That is exactly what I did.

I checked the files as well.

There is one record that exist in before file that is not in after file.

But it does not pull the delete code for some reason.

I also set the delete code in the option.

Any pointers?

[us1aslam1us]

For testing purpose keep a copy or peek stage after the CDC stage and check the data.

[DSguru2B]

Is the delete record the first record in the file by any chance? If it is then make sure you have "First line is column name" to false.

[ray.wurlod]

Are you perhaps using customized code values and have chosen the same code value for two different diff cases?

[balajisr]

Set Drop output to false for all(Insert,update,delete,copy) so that we can know the code of the deleted record.Also set log statistics to true and post the results.

[vick]

thanks!

I have set Drop output to false for all(Insert,update,delete,copy) and set the log stats to true as well.

On the DSDesigner the performance stats show 6000(rows from before FILE) and 6500 (rows from after FILE) and the target Oracle stage shows 9688 rows.

But the DSDirector shows 1600 inserted, 8088 rejected & 0 updated.

Is it based on the key match criteria?

Pl suggest.

TIA

[vick]

Thanks a lot folks.

I think I figured that. I was doing an upsert into the table, changed it to load---->append and this took care of the 8000 odd records that were being rejected. (I think I had the wrong SQL for Insert & Update which was rejecting records)

I resolved the issue with Delete code as well. Earlier I was not giving the Change Value----->Value (Column Values to be changed).

I now give the changes for all 44 columns and set Drop output to false for all(Insert,update,delete,copy) and set the log stats to true as well.

Again thanks a lot for all your inputs

[vick]

The CDC stage I have currently works on the combination of 2 keys (ID & S_No)
when comparing the 2 files (previous day file & current day file).

I need to compare the files based on 2 sets of keys. For instance

ID & S_No is the first combination
ID & V_No is the second combination

Is it possible to do that in the same run or do I have to run it twice with different key combinations.

TIA

[balajisr]

Once your problem is solved set log statistics to false and remove unnecessary change codes.