Page 1 of 1
update job aborted
Posted: Thu May 22, 2008 12:39 pm
by kirankota79
Hi...i have a job created for updating a column in the oracle table. The job got aborted in the middle while updating. Is it possible to update from the point where it got aborted?. The commit interval is for every 2000 rows.
Re: update job aborted
Posted: Thu May 22, 2008 1:05 pm
by wesd
kirankota79 wrote:Hi...i have a job created for updating a column in the oracle table. The job got aborted in the middle while updating. Is it possible to update from the point where it got aborted?. The commit interval is for every 2000 rows.
Not with a commit interval like that. You have to start from the beginning, but since it's update it shouldn't be a big issue. The question is why did the job abort?
Re: update job aborted
Posted: Thu May 22, 2008 1:30 pm
by kirankota79
wesd wrote:kirankota79 wrote:Hi...i have a job created for updating a column in the oracle table. The job got aborted in the middle while updating. Is it possible to update from the point where it got aborted?. The commit interval is for every 2000 rows.
Not with a commit interval like that. You have to start from the beginning, but since it's update it shouldn't be a big issue. The question is why did the job abort?
Hi wesd....thanks for the reply....my scenario is:
i have ajob like this:
i/p Oracle Stage---------->Transformation----------->o/p Oracle stage
in the input iam selecting the rows need to transformed....in the o/p i update the transformed values. The input and out stages contain the same table. So selecting and updating the same table. If the table has a KEY column i use it as a key for updating, if the table doesn't have any key columns i am using ROWID (since it is unique) as key for updates.
If the job aborts in the middle part of the table gets updated with transformed values and part of them remains as original values. Now i cannot rerun the job, since i dont want to transform the transformed values. Is this approach a good one.
I can also use sequential file and create a new job for updating. But the previous one is much faster and simpler.
Posted: Thu May 22, 2008 3:31 pm
by ray.wurlod
Clearly this approach, as you have implemented it, is not a good one.
You must design any restart capability, and this involves keeping accurate records of (at least the count of) which rows were sent to the target table and, indeed, which ones failed. One way is to tag the target records with a unique run ID and/or run timestamp.
You can think about what's needed for the restart logic - it will vary depending on particular requirements, and how you select the records to be updated.