Rollback the data if the datastage job get aborted

chulett · Post by **chulett** » Tue Dec 22, 2009 10:40 am

Check the Transaction Handling tab.

pandujoy · Post by **pandujoy** » Tue Dec 22, 2009 10:48 am

thanks duke.....

since i will be getting 1 to 3 rows per transaction ........and if 1 row loads in the 1st database table and while loading the second row in the seconds database table the job is getting aborted resulting the nullfying the rollback function

Abhijeet1980 · Post by **Abhijeet1980** » Wed Dec 30, 2009 8:30 am

I always wanted this functionality to be a provided by the software tool and never part of any custom programming done by us.

Business logic is what we are expected to code and given money for. Certainly not for the integrity and consistency checks.

Do you see any worthiness in a program of 5 lines (logic) and 5K (enforcing atomicity, integrity and consistency checks).

Rollback, Undo segment - Leave the worries to your DBA. He gets paid for that and not you.

Anyway, coming back to the point.

-----------------------------------------------------------------------------------
Some prejudices to help us.

1. Rollback and Commit are very intensive operations (both memory and computation) esp when you are dealing with a volume.

2. When theres no volume - Why worry about the consistency, then. Redo it as many times as needed. Update-Insert logic might be of a great help.

Your case look like a ODS than a DW since it is ETL in almost real time basis.

How this may be achieved be redesigning the jobs

Job_1. Dump everything into a table (unnormalized) using real time job with a key and a status which you may use later for selection purpose.

Job_2. Select from this dump table all records that are required (A day or month or the past hour), load it to the target and then update the status in dump.

Hope this helps even in impractical for you.

eostic · Post by **eostic** » Wed Dec 30, 2009 8:43 am

I have a draft entry for my blog on this subject, but never got around to finishing it and publishing it. If you use a Transformer in Server with ODBC, as Craig notes, and get yourself very familiar with the "Transaction" settings, you can set up (for example) four links coming from your Transformer...

one for your first table
one for your second table
one for rollback
one for commit

All tables must be in the same database (thus going to one target ODBC Stage and managed by one connection handle), and they must be able to be managed from the same Transformer Stage. The rollback and commit links are just user-defined SQL with either "COMMIT;" or "ROLLBACK".....you decide on your own, in a constraint, when and whether each should fire........

Ernie