Commit interval in oracle enterprise stage

nagarjuna · Post by **nagarjuna** » Tue Jul 28, 2009 5:12 pm

Hi ,

I am having a oracle enterprise stage as target.This job is restartable ( checkpointed the sequence from which this is called ) .I am using upsert mode .

So , if my job aborts and again i restarted the sequence ( without resetting ) then will my job load the records from the point it has failed ? or will it be dependent on environment variables
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL

Thank you all
Nag

nagarjuna · Post by **nagarjuna** » Tue Jul 28, 2009 5:26 pm

Note that i have not set the variable APT_ORAUPSERT_COMMIT_ROW_INTERVAL to 0

chulett · Post by **chulett** » Tue Jul 28, 2009 6:46 pm

Sequence restartability is completely different from the restartability you mean at the job level. It will simply start the job over again and without anything specific to handle previously committed rows will just attempt to load everything again. You may be OK with the upsert mode, however, as inserts would become updates.

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 8:31 am

Craig thanks for the response .

Let me put my understanding in this way ...

If there are 4 jobs in a sequence .The sequence is restartable . My sequence got aborted during 3rd job .This 3rd job loads data in to oracle table .Suppose there are 10000 records has to be loaded and job got aborted after loading 6000 .Then if restart the sequence then again it will start from job3 but it starts loading from record 1 .

Please correct me if i am wrong .

Thanks
Nag

miwinter · Post by **miwinter** » Fri Jul 31, 2009 8:33 am

Correct Nag

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 8:50 am

Thanks for the quick confirmation ...

But how to handle these type of scenarios.Because i am loading the same data again into the table .Could you please suggest any idea to handle this ??
Thank you all
Nag

miwinter · Post by **miwinter** » Fri Jul 31, 2009 8:52 am

You'll need to either include some handling to make a call to a job/script to backout the partial data loaded, or to make it restart (at job level) from where it left off, there is nothing built in to cater for it, so it's a design you need to come up with.

You could also, of course, have no commit level, which would mean it's all or nothing for load, but this carries implications on the database side.

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 8:53 am

Can i make APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0 to handle this ? Please let me know your thoughts on this

miwinter · Post by **miwinter** » Fri Jul 31, 2009 8:54 am

Yes, that covers the second point I made above.

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 9:05 am

You have mentioned it will have some effect on DB side .Could you please tell what effect it can have ??

ArndW · Post by **ArndW** » Fri Jul 31, 2009 9:07 am

The main effect is that your database rollback space will need to be large enough to contain the whole transaction. If it isn't the job will abort.

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 9:13 am

I have very little knowledge on DB rollback space ...Can i find any stats saying that to handle this much data i need this much rollback space ....Sorry if i am asking very basic question ...

chulett · Post by **chulett** » Fri Jul 31, 2009 9:20 am

No, too many variables. And that's really a chat you need to have with your DBA anyway.

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 9:30 am

ok thanks everyone .....My doubts got resolved ...

nagarjuna · Post by **nagarjuna** » Fri Jul 31, 2009 10:25 am

nagarjuna wrote:Can i make APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0 to handle this ? Please let me know your thoughts on this

Hi Everyone ,

Just one more quick question ....what about if the load is ising sqlldr or direct load ? This env has no meaning in that case ....So what is the process or design you suggest to handle this ??