Commit interval in oracle enterprise stage

datisaq · Post by **datisaq** » Fri Jul 31, 2009 10:41 am

Hi,
Can anyone please elaborate on APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0?

I mean for the job if you have set this env variable to zero, then how the rows will get committed in DB?Do we need to manually run the commit query?

If this questions sounds very silly, please excuse me for that..

miwinter · Post by **miwinter** » Fri Jul 31, 2009 10:44 am

The rows will be committed after the last row is written to the database. i.e. a single commit operation.

chulett · Post by **chulett** » Fri Jul 31, 2009 10:56 am

When the stage "closes", technically.

chulett · Post by **chulett** » Fri Jul 31, 2009 10:58 am

nagarjuna wrote: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 ??

Not really all that quick of a question, but basically - failed sqlldr loads are not a Good Thing and generally require manual (read: DBA) intervention get to the table and/or indexes back in working order. It is completely different from the rather innocuous rollback of uncommitted rows.

datisaq · Post by **datisaq** » Fri Jul 31, 2009 11:42 am

But nagarjuna cannot be achieve..
Let's say the job(with commit interval as zero) got aborted after loading 6000 rows out of 10000 rows.It loads only the 6000 rows; when we restart the job it starts from the 1st row since it's a job not a sequence..

This leads to loading the records again..

May be i didn't understand your replies, please correct me..

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

datisaq wrote:But nagarjuna cannot be achieve..
Let's say the job(with commit interval as zero) got aborted after loading 6000 rows out of 10000 rows.It loads only the 6000 rows; when we restart the job it starts from the 1st row since it's a job not a sequence..

This leads to loading the records again..

May be i didn't understand your replies, please correct me..

Hi ,

Because job got aborted and we will be putting environment variable value to 0 it wont commit

datisaq · Post by **datisaq** » Fri Jul 31, 2009 11:49 am

Thanks nagarjuna,miwinter and craig for explaining me...

dhiren · Post by **dhiren** » Sun Aug 30, 2009 11:27 am

While going through this post, I have a question . If my job is loading two tables in upsert mode , then will the setting of variable APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0 ensure that it is applicable for both the tables.

Just to make this simple enough: Will this Env variable work at stage level or at job level ?

chulett · Post by **chulett** » Sun Aug 30, 2009 2:50 pm

Job level.

DSDexter · Post by **DSDexter** » Thu Sep 24, 2009 1:54 am

Chulet,
It's only for Oracle Enetrprise stage since enetrprise stage don't have any Transaction level settings, either it will take 5000 or 5 Sec as the default Commit interval for commit.

Then what about 'DRS Stage',which has got Transaction level & array size settings as well.
If we are using more than one DRS stages(Nearly 5) in a job & Environmental Variable "APT_RDBMS_COMMIT_ROW_INTERVAL", set to zero then will it really work by overriding the stage level Transaction level.

rajesh223 · Post by **rajesh223** » Tue Aug 03, 2010 5:41 pm

nagarjuna wrote:
datisaq wrote:But nagarjuna cannot be achieve..
Let's say the job(with commit interval as zero) got aborted after loading 6000 rows out of 10000 rows.It loads only the 6000 rows; when we restart the job it starts from the 1st row since it's a job not a sequence..

This leads to loading the records again..

May be i didn't understand your replies, please correct me..
Hi ,

Because job got aborted and we will be putting environment variable value to 0 it wont commit

Hi,
I tried to run the job with APT_ORAUPSERT_COMMIT_ROW_INTERVAL =0 but failed because datastaeg job does not all to run with value 0 in APT_ORAUPSERT_COMMIT_ROW_INTERVAL