Oracle append method without duplicates entry

premupdate · Post by **premupdate** » 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

ray.wurlod · Post by **ray.wurlod** » Mon Apr 20, 2015 6:36 pm

If every load is uniquely identified (perhaps with a run ID, perhaps with a created timestamp) you can use these identifiers to roll back to a known good point in the event that you need to re-load.

premupdate · Post by **premupdate** » Mon Apr 20, 2015 9:28 pm

Thanks Ray,

But the table dont have an option like timestamp or row id...is there anyway to achieve this.The only column is Extract date where we should have manual interference incase of any issues which is not allowed as per process.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 20, 2015 10:54 pm

To answer your first question, "no".

Why have Extract Date if it's not permitted to be used? Resist stupid requirements!

premupdate · Post by **premupdate** » Tue Apr 28, 2015 9:04 pm

Hello All,

Found a solution of using the strength of Before SQL in Oracle Stage where it will have a delete statement with extract date in it.So whenever a rerun happens with partial records in table,the before SQL will delete the records with date matching before being inserted.

Thanks,
Prem

ray.wurlod · Post by **ray.wurlod** » Tue Apr 28, 2015 10:49 pm

I thought you weren't allowed to use the Extract Date ?