commit frequency in Oracle Enterprise stage

nirdesh2 · Post by **nirdesh2** » Tue May 26, 2009 11:26 pm

Hi,

Is there any way to increase the commit frequency when loading the data into oracle table using Load and Appened method. Right now Commit points occurs at 64 rows by default .Also, If i used OPTION(DIRECT=FALSE,PARRELEL=TRUE,ROWS=1000), then commit point occurs at 587.

arvind_ds · Post by **arvind_ds** » Wed May 27, 2009 12:34 am

Using DataStage Administrator set the value of $APT_RDBMS_COMMIT_ROWS parameter to desired value.

nirdesh2 · Post by **nirdesh2** » Wed May 27, 2009 2:55 am

I think this parameter is used $APT_RDBMS_COMMIT_ROWS DB2 database. Still it is not considering this parameter values for commit.

BenoitCayla · Post by **BenoitCayla** » Wed May 27, 2009 3:29 am

nirdesh2 wrote:I think this parameter is used $APT_RDBMS_COMMIT_ROWS DB2 database. Still it is not considering this parameter values for commit.

The settings of this variable apply to the supported parallel database engines : DB2, Teradata and Oracle.

ArndW · Post by **ArndW** » Wed May 27, 2009 5:02 am

What did you specify in your stage as "transaction size"?

nagarjuna · Post by **nagarjuna** » Wed May 27, 2009 5:45 am

If DIRECT = TRUE then is it not auto committed ...i think when using load option there is no commit interval ...for only upsert we have commit interval ..please correct me if i am wrong ..

sanjay · Post by **sanjay** » Wed May 27, 2009 6:25 am

Hi

need to set bindsize & readsize

example

$APT_ORACLE_LOAD_OPTIONS=OPTIONS(SKIP_INDEX_MAINTENANCE = TRUE,bindsize=8250000,readsize=8250000,rows=2000)

Thanks
Sanjay

nagarjuna wrote:If DIRECT = TRUE then is it not auto committed ...i think when using load option there is no commit interval ...for only upsert we have commit interval ..please correct me if i am wrong ..

chulett · Post by **chulett** » Wed May 27, 2009 6:55 am

That's certainly one way. The specific Oracle APT variables are mentioned here. And yes, a "direct path load" does no commits, that whole mechanism is bypassed which is part of where the speed comes from.

Kryt0n · Post by **Kryt0n** » Wed May 27, 2009 5:15 pm

nagarjuna wrote:If DIRECT = TRUE then is it not auto committed ...i think when using load option there is no commit interval ...for only upsert we have commit interval ..please correct me if i am wrong ..

Have no idea about the DIRECT option but sounds fair enough comment. However, there is a commit interval for the load option. We have DIRECT set to FALSE (we load in parallel) and we do commits... more frequently than I would like but have not tried to play with the intervals.

I believe there are two APT variables that need changing, one is for number of rows and the other a time interval

EDIT: looking at the environment variables, I must be thinking about the upsert options so ignore my last sentence...

chulett · Post by **chulett** » Wed May 27, 2009 7:10 pm

Right, sqlldr with DIRECT=FALSE is a 'conventional load' and does normal inserts, so the commit interval plays a role. When DIRECT=TRUE there are no commits done.

nirdesh2 · Post by **nirdesh2** » Sun May 31, 2009 1:42 am

Thanks all of you for sharing your thoughts. It helped me a lot to understand the load process .