Different ways to recover scd type 2 data load failure

[hitmanthesilentassasin]

Hi ,

We have implemented scd type 2 functionality using datastage px jobs. However, We would like to automate the recovery of the data when job fails in between the load. Could you please suggest me the different possible methods? and we should be ok even if we have to go beyond ds like using a shell script or routine to do so.

[chulett]

You might want to explain what you mean by 'the recovery of the data' here.

[hitmanthesilentassasin]

I meant reverting the table to the state that it was before the job load started.

[kcbland]

That would have required you to build an ETL framework that captures inserts and updates and the before images of the rows updated. You would delete your inserts and update your updates with the before-image. Otherwise, you're in to using database table recovery procedures such as logging to rollback a table post-load.

[hitmanthesilentassasin]

I am more looking into multiple options from which we can decide from what suits best for our needs. Probably a some web link reference will also give me a starting point as I am not able to find the right web site which describes recovery options