DSXchange

Hi,

First time I am posting a topic.
I am facing some issue with Oracle OCI stage commit in Datastage 8.1 PX.
My requirement is while doing an upsert either commit all the records if job successful or not to commit any records if job aborts. I came to know that this can be achieved by changing the APT_OACLE_UPSERT_COMMIT_ROWS_INTERVAL to zero and APR_ORACLE_UPSERT_COMMIT_TIME_INTERVAL to zero.
I have set this but after that while running the rows it says that these values should be greater than zero and aborting the job.
Then I tried this by setting the variables commit_interval to 10M and time interval to 3600(some big values ), but here while aborting the jobs it commits (ie inserted few records in target stage). Transaction size property available only on server stages so I can't do that also.
Could anyone please help me on this.

Reg
Prasanth

Welcome. Please edit your post to include your DataStage version and O/S.

Please note the forum where I've moved this post, it is the proper forum for discussions of anything PX specific. Please keep that in mind when posting in the future. Thanks.

ps. Moved here from the FAQ Discussion forum. That's were you propose topics you think would make a good FAQ.

As to your question, I don't think I've ever seen a response here that there is a working solution to an 'all or nothing' load in PX. As noted, setting the transaction size to zero works great in the Server world but doesn't seem to have an equivalent here.

Has anyone found a way to implement this?

There are 2 ways that I am aware of to achieve an "all or nothing" commit in a parallel job:

1) Run the target database stage in sequential mode with a transaction size of zero (with the obvious performance implications)
2) Use the Distributed Transaction Stage (DTS)

Mike

1. I dont have Oracle at this moment.
2. I havent done this before.

But can't we use Before & After SQL Statement and make the auto commit off and on?

DS User

Looks like you can set the Record count value in the Oracle Connector stage to zero to mean 'only commit at the end'... have you tried that? Also note that the OCI stage supports the same concept on the 'Transaction Handling' tab, from what I recall.

You can set it to zero, but would have to run it sequentially to get an all or nothing result. The Oracle connector is going to have one process for each node in the configuration running and each process will handle its own commits.