Page 1 of 2
Commit interval in oracle enterprise stage
Posted: Tue Jul 28, 2009 5:12 pm
by nagarjuna
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
Posted: Tue Jul 28, 2009 5:26 pm
by nagarjuna
Note that i have not set the variable APT_ORAUPSERT_COMMIT_ROW_INTERVAL to 0
Posted: Tue Jul 28, 2009 6:46 pm
by chulett
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.
Posted: Fri Jul 31, 2009 8:31 am
by nagarjuna
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
Posted: Fri Jul 31, 2009 8:33 am
by miwinter
Correct Nag
Posted: Fri Jul 31, 2009 8:50 am
by nagarjuna
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
Posted: Fri Jul 31, 2009 8:52 am
by miwinter
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.
Posted: Fri Jul 31, 2009 8:53 am
by nagarjuna
Can i make APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0 to handle this ? Please let me know your thoughts on this
Posted: Fri Jul 31, 2009 8:54 am
by miwinter
Yes, that covers the second point I made above.
Posted: Fri Jul 31, 2009 9:05 am
by nagarjuna
You have mentioned it will have some effect on DB side .Could you please tell what effect it can have ??
Posted: Fri Jul 31, 2009 9:07 am
by ArndW
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.
Posted: Fri Jul 31, 2009 9:13 am
by nagarjuna
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 ...
Posted: Fri Jul 31, 2009 9:20 am
by chulett
No, too many variables. And that's really a chat you need to have with your DBA anyway.
Posted: Fri Jul 31, 2009 9:30 am
by nagarjuna
ok thanks everyone .....My doubts got resolved ...
Posted: Fri Jul 31, 2009 10:25 am
by nagarjuna
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 ??