Hi
We are using the Oracle Enterprise stage to read and write to our database. Is it possible to roll back transactions in Datastage if a sequence fails?
ie in the sequence
job1 writes to db, succeeds
job2 fails for some reason
job3 never starts because job2 failed
Is there a feature in Datastage which allows for the rollback of the job1 transactions?
commit on sequence success
Moderators: chulett, rschirm, roy
Hi,
You cannot do the rollback for data which is inserted through Job1. You can do the followings.
a. Add a lookup and verify for primary key, insert the data if it is not present else update.
b. Use insert and update after defining the primary key in database.
c. If it is staging area, always do truncate and insert.
d. Run only the job that is failed, job 1 will not be called. Only Job 2 will be called. If you use any of the above approach (a - c), duplicates can be avoided.
You cannot do the rollback for data which is inserted through Job1. You can do the followings.
a. Add a lookup and verify for primary key, insert the data if it is not present else update.
b. Use insert and update after defining the primary key in database.
c. If it is staging area, always do truncate and insert.
d. Run only the job that is failed, job 1 will not be called. Only Job 2 will be called. If you use any of the above approach (a - c), duplicates can be avoided.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There's no rollback - job 1 will have executed a COMMIT. The usual approach is to mark all loads with, say, a unique run number or timestamp. Then you can create "rollback" jobs that delete rows that have been inserted with this identifier. If you need to "rollback" updates, then your original design needs to capture a before image at least of records to be updated.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.