Oracle Stage Commit Point

[Nick_6789]

Hi all,

I have a small question for research purposes.

I have a job that loads data from an sql server stage to Oracle stage. My question involves what is the commit interval set for the oracle stage by default?

Let's say my sql server source involves 10 million records, I don't think it will only commit after inserting 10 million records? It would be disasterous if the connection cuts of in between.

I can't seem to find a setting in Oracle Stage pertaining to this option (Specifying commit intervals).

Would any of you guys know? Thanks in advanced.

[Nageshsunkoji]

Hi,

I think in your case, you can use UR ( Uncomitted Read or dirty read) in the SQL query itself by using WITH UR at the end. Usually we are doing the same thing in DB2 stage.

[chulett]

There are APT variables that control both the Oracle commit level and frequency, but don't remember their exact name off the top of my head. I'm sure a search of the documentation or this forum will turn them up.

[thebird]

The Environment variables available to control the commit intervals are -

APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL

These two environment variables work together to specify how often target rows are committed when using the Upsert method to write to Oracle.
Commits are made whenever the time interval period has passed or the row interval is reached, whichever comes first. By default, commits are made every 2 seconds or 5000 rows.


The Bird

[sajarman]

Hi,

I appreciate if someone can throw light on how to decide the values for Row commit and Commit Time intervals for Oracle.

Thanks,
Sajarman.

[ArndW]

You will need to talk to your DBA in order to make this decision, if you have no business rules for this. The less commits you make, the faster a load can be, but the more the rollback segments fill up. If they reach their limit you will have errors. If you commit too often when you don't have to you end up having a lot of system overhead.