Oracle Row Commit

binay · Post by **binay** » Thu Jun 05, 2008 9:43 am

Hi,

Where is the oracle row commit defined in DataStage? Administrator? If yes, which variable?

As a default, how many rows are committed by the Oracle Stage in DataStage in an Oracle table? Is it something we can control or change in DataStage to increase the number of commit in a particular interval?

ArndW · Post by **ArndW** » Thu Jun 05, 2008 9:55 am

The environment variables, which you can include in the job parameters and change the defaults values, contain:

$APT_ORAUPSERT_COMMIT_ROW_INTERVAL (5000 row default)
$APT_ORAUPSERT_COMMIT_TIME_INTERVAL (2 second default)

kirankota79 · Post by **kirankota79** » Fri Jun 06, 2008 1:26 pm

hi andrw: i want to update some columns in a table and if the job aborts in the middle i dont want to commit anything, i want want to leave as it is as before the job started. Only if job runs successfully i want to commit. Where can i set this in the datastage.

kirankota79 · Post by **kirankota79** » Fri Jun 06, 2008 1:28 pm

hi andrw: i want to update some columns in a table and if the job aborts in the middle i dont want to commit anything, i want want to leave as it is as before the job started. Only if job runs successfully i want to commit. Where can i set this in the datastage.

kirankota79 · Post by **kirankota79** » Fri Jun 06, 2008 1:33 pm

hi andrw: i want to update some columns in a table and if the job aborts in the middle i dont want to commit anything, i want want to leave as it is as before the job started. Only if job runs successfully i want to commit. Where can i set this in the datastage.

kirankota79 · Post by **kirankota79** » Fri Jun 06, 2008 1:40 pm

sorry to post so many times....haven't done wantedly....some network issue so i pressed submit button many times.

keshav0307 · Post by **keshav0307** » Sat Jun 07, 2008 2:46 am

it will be better to find out why the job was failed and solve that.
why you don't want commit interval, if the job abort don't you will restart the job?
if there are large number of records for update and insert then you may possibly hit the rollback segment error.

ArndW · Post by **ArndW** » Sat Jun 07, 2008 3:44 am

Have you tried setting $APT_ORAUPSERT_COMMIT_ROW_INTERVAL to a value higher than your number of rows?

chulett · Post by **chulett** » Sat Jun 07, 2008 6:18 am

In Server you would set any such 'commit interval' to zero for an 'only commit once at the end' load. Is there no such equivalent in PX?

kirankota79 · Post by **kirankota79** » Sat Jun 07, 2008 10:39 am

does the datastage settings overwrite the database settings?

chulett · Post by **chulett** » Sat Jun 07, 2008 11:08 am

What settings are you asking about? There are no database settings for commits.

kirankota79 · Post by **kirankota79** » Sat Jun 07, 2008 11:53 am

$APT_ORAUPSERT_COMMIT_ROW_INTERVAL and
$APT_ORAUPSERT_COMMIT_TIME_INTERVAL

if i set both to zero . then there won't be any commit happens till the complete update happens right?