Oracle Enterprise Stage

KStack · Post by **KStack** » Mon Feb 28, 2005 11:08 am

Any suggestions on restarting Oracle Enterprise Stages. Is it possible to capture the number of commit points and use this to restrict the records for the restart?

The easiest way is to have no commits and let it roll back on failure, I was wondering if anyone had a better way.

Thanks

Kevin

ArndW · Post by **ArndW** » Mon Feb 28, 2005 11:19 am

KStack,

as you said, by far the easiest is to have one big commit at the end of job execution. Should the job abort for some reason the restartability is essentially re-running the job. Easy, simple and straightforward.

Unfortunately, it is not fast

For jobs that run for hours this is not a good solution. Also, for big jobs you might run into timeout issues as well as commit size constraints.

If your input data is in the same order each run (i.e. a sequential file as opposed to a SELECT on a table) then you can program around a restart in DataStage by getting the number of rows processed from a previous run (located in the job's log file) and then restarting from that row number minus a modulus for whatever commit frequency you choose. This can be done without too extensive effort and can also be fully automated.

So the choice is yours - but I would prefer to keep the jobs simple and won't program around the restartability issue unless I really have a reason to do so.

bibhudc · Post by **bibhudc** » Mon Feb 28, 2005 1:10 pm

One way you could avoid re-processing all the rows again is to store the records in a flat file before you insert into the Oracle table. This of course gets complicated if you have multiple targets in a sequential processing situation.

dsxuserrio · Post by **dsxuserrio** » Mon Feb 28, 2005 11:03 pm

Another way of addressing the problem would be to load into empty work/staging table. From staging/work table to target (actual ) table could be done fully by oracle. (Using a package ). That works great for us with huge volumes.

ArndW · Post by **ArndW** » Tue Mar 01, 2005 2:05 am

As dsxuserrio stated, his approach is a very efficient one; I'd like to add that if you can have your DBA partition your target tables in such as way as to make each load go into a specific partition, then you wouldn't need to copy the data from the "staging" or interim tables, you would hust activate the partition.