Page 1 of 1
commit frequency in Oracle Enterprise stage
Posted: Tue May 26, 2009 11:26 pm
by nirdesh2
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.
Posted: Wed May 27, 2009 12:34 am
by arvind_ds
Using DataStage Administrator set the value of $APT_RDBMS_COMMIT_ROWS parameter to desired value.
Posted: Wed May 27, 2009 2:55 am
by nirdesh2
I think this parameter is used $APT_RDBMS_COMMIT_ROWS DB2 database. Still it is not considering this parameter values for commit.
Posted: Wed May 27, 2009 3:29 am
by BenoitCayla
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.
Posted: Wed May 27, 2009 5:02 am
by ArndW
What did you specify in your stage as "transaction size"?
Posted: Wed May 27, 2009 5:45 am
by nagarjuna
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 ..
Posted: Wed May 27, 2009 6:25 am
by sanjay
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 ..
Posted: Wed May 27, 2009 6:55 am
by chulett
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.
Posted: Wed May 27, 2009 5:15 pm
by Kryt0n
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...
Posted: Wed May 27, 2009 7:10 pm
by chulett
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.
Posted: Sun May 31, 2009 1:42 am
by nirdesh2
Thanks all of you for sharing your thoughts. It helped me a lot to understand the load process .