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).
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
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.