Commit Frequency

[Atanu]

How to handle commit frequency in DataStage Parallel Jobs and where this property is to set in job. :?:

[srekant]

you can achieve it by environement variable $APT_ORAUPSERT_COMMIT_ROW_INTERVAL

[ray.wurlod]

That's the old way, but still works. Nowadays most stage types have a property called Commit Row Interval.

[ArndW]

I'll be darned if I can find that property for the Oracle OCI stage in Px, though.

[ray.wurlod]

So how come you're not using the Oracle Enterprise stage?

[ArndW]

Ummm.. I was wrong, we are using it; and it doesn't (have a commit row interval)

[ray.wurlod]

I was wrong, too. It's the DB2 Enterprise stage, and only available when the Write Method property is "Write".

[bcarlson]

The variable is APT_RDBMS_COMMIT_ROWS. We usually set it to something divisible by 4096 (why? who know). Either 8192 or 16384 are common in our processes.

[rishi]

If you want to do commit on Updates in DB2:
InDB2 Enterprise stage we can specify when to commit by using two parameters:
1) Time Commit Interval (2 seconds by default)
2) Row Commit Interval (2,000 by default. We can keep its value only in multiples of Array Size(default 2,000))
But what we found is that Row Commit Interval parameter is of no use as whatever value you give to Array Size parameter, commit happens on that. So let's say if Row Commit Interval=10,000 and Array Size=5,000 and the job aborts after updating 9,000 records instead of rolling back to 0 it rolls back to 5,000 (Array Size).

So one can keep Array Size and Time Commit Interval parameters. Commit will happen for lesser of the two parameter values.