DSXchange

Hi
I have a really interesting scenario
" Initially i run the sequencer. There are 6 jobs in the sequencer. In the 2nd job i will update an oracle table. later all the jobs will be run. but if any job (3,4,5,6) job fails, i have to rollback the data in the oracle table.(i want the data in the oracle table before updation took place).Is this scenario can be met with the datastage

Not via rollback since you commit in job #2. What you need to do is to include some mechanism for identifying the rows that are loaded (such as a unique run ID or a timestamp) and delete these should that become necessary. If you need to "rollback" updates or deletes, it becomes a little more convoluted, in as much as you need to have a "before image" of the target table stored somewhere before running the DataStage jobs, then run other recovery jobs as necessary.

just4u_sharath wrote:Is this scenario can be met with the datastage

Just for the record, this particular scenario can't be met with any tool. As noted, comitted is comitted, so no rollback is available after the fact. Now, that doesn't mean you can't play fancy games with some kind of 'before image' as Ray notes, or with flashback perhaps, but a simple rollback is off the table.

I particularly know what row is going to be updated. How can i create a before image of the table with datastage itself. even i create a before image of the table, can i recover it using the datastage. Imp.point is this recovery should be automatic. I cannot go and run the recovey job, after knowing my job failed. As craig said, what is a flash back

ray.wurlod wrote:Not via rollback since you commit in job #2. What you need to do is to include some mechanism for identifying the rows that are loaded (such as a unique run ID or a timestamp) and delete these should that become necessary. If you need to "rollback" updates or deletes, it becomes a little more convoluted, in as much as you need to have a "before image" of the target table stored somewhere before running the DataStage jobs, then run other recovery jobs as necessary.

I particularly know what row is going to be updated. How can i create a before image of the table with datastage itself. even i create a before image of the table, can i recover it using the datastage. Imp.point is this recovery should be automatic. I cannot go and run the recovey job, after knowing my job failed. As craig said, what is a flash back

just4u_sharath wrote:As craig said, what is a flash back

A simple Google will turn up information on the technology, which was introduced in 9i and improved in subsequent releases. Have a chat with your DBA to see if it is something they are willing and/or able to support. One such URL:

Oracle Flashback Technology

store the records you are going to update in a temporary table, before you actually update it, and if your later stage jobs failed then delete the updated records and insert the old records from the temporary table.