Oracle append method without duplicates entry
Posted: Mon Apr 20, 2015 6:17 pm
Hello Folks,
My Job design
Job 1: Teradata Table -> TFM -> DataSet
Job 2: DataSet ->TFM -> Oracle
Requirement: To load all data from TD in to oracle without any transformation or conditions
The TD table is temporary table which will get loaded in a daily basis with a truncate and load option.
The Oracle table load method is append method where business needs all data to be stored.No key columns defined in the table.The table has extraction date as a column which will be populated from the scheduler.
In normal scenario, the above jobs will run smoothly.But if there are failures in the job ,during rerun the same records might get inserted again as its an append method.
The expected volume of data is around 1400 records with 4 MB size with 56 columns in each row.
My option:
Using Change capture stage with All Keys,Explicit values where all the 55 columns defined as key columns(except the date column) for the stage to compare and then identify the insert record for oracle table.
At the maximum there is a possibility of 1200 MB size in the before dataset(Oracle table) of Change capture stage.
Please let me know if this is the option which can be tried or is there any other option to avoid duplicates loads during a job failures
My Job design
Job 1: Teradata Table -> TFM -> DataSet
Job 2: DataSet ->TFM -> Oracle
Requirement: To load all data from TD in to oracle without any transformation or conditions
The TD table is temporary table which will get loaded in a daily basis with a truncate and load option.
The Oracle table load method is append method where business needs all data to be stored.No key columns defined in the table.The table has extraction date as a column which will be populated from the scheduler.
In normal scenario, the above jobs will run smoothly.But if there are failures in the job ,during rerun the same records might get inserted again as its an append method.
The expected volume of data is around 1400 records with 4 MB size with 56 columns in each row.
My option:
Using Change capture stage with All Keys,Explicit values where all the 55 columns defined as key columns(except the date column) for the stage to compare and then identify the insert record for oracle table.
At the maximum there is a possibility of 1200 MB size in the before dataset(Oracle table) of Change capture stage.
Please let me know if this is the option which can be tried or is there any other option to avoid duplicates loads during a job failures