How to set commit frequency in Oracle Entreprise stage

[Atanu]

Hi to DS gurus,

I am using Oracle enterpeise stage in my Parallel job which is as :
Sequential file -> transformer -> Oracle Enterprise Target table

Write method is : UPSERT

My question is how to set TRANSACTION SIZE.

Their is one option host array size.How it will be benefical in UPSERT mode?

Thanks in advance.

[chulett]

Search is your friend.

Try this post.

[elavenil]

As of my best knowledge, the transaction size (Commit interval) should be set to 1 for Upsert mode due to additional check is required to identify the existence of the record. So transaction size is set to 1.

Hope TWH.

Regards
Saravanan

[SriKara]

Set the transaction size to 1 ??
The scenario which you mentioned may arise only if you have an Insert record and an Update record with the same key in the same input dataset.

I think if the source is expected to give an insert and an update for the same key in the same loading, the ETL design should be altered to take care of sequence of loading. IMHO.

[jasper]

If you're working with parallell processes it might indeed be best to set this to 1.
I've also come accross deadlock situation, that had nothing to do with bad design. Only way to avoid this is if you can partition your table and get datastage partiotioned in the same way.

[Lawrence]

Does anybody know how to set the commit interval for the 'load' - Write Method. My job log looks like it commits every 5000 rows or so, and I can't find any setting in the ctl file that Datastage generates.

[alisoviejo]

Use the env variable "Oracle commit row interval" , search for t and set the value , the default is 5000