Oracle OCI Stage Commit for UPsert

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
philip_prasanth
Participant
Posts: 3
Joined: Mon Oct 19, 2009 8:51 am
Location: asd

Oracle OCI Stage Commit for UPsert

Post by philip_prasanth »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Post by SURA »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
kwwilliams
Participant
Posts: 437
Joined: Fri Oct 21, 2005 10:00 pm

Post by kwwilliams »

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.
Post Reply