commit on sequence success

[dsguyok]

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?

[oacvb]

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.

[ray.wurlod]

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.