DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
philip_prasanth
Participant



Joined: 19 Oct 2009
Posts: 3
Location: asd
Points: 44

Post Posted: Sat Dec 10, 2011 4:00 am Reply with quote    Back to top    

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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42926
Location: Denver, CO
Points: 221381

Post Posted: Sat Dec 10, 2011 9:18 am Reply with quote    Back to top    

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

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42926
Location: Denver, CO
Points: 221381

Post Posted: Sun Dec 11, 2011 8:58 am Reply with quote    Back to top    

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

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1020
Location: Tampa, FL
Points: 6593

Post Posted: Sun Dec 11, 2011 5:29 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
SURA



Group memberships:
Premium Members

Joined: 14 Jul 2007
Posts: 1229
Location: Sydney
Points: 9005

Post Posted: Sun Dec 11, 2011 5:42 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42926
Location: Denver, CO
Points: 221381

Post Posted: Sun Dec 11, 2011 5:56 pm Reply with quote    Back to top    

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

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
kwwilliams
Participant



Joined: 21 Oct 2005
Posts: 437

Points: 3209

Post Posted: Mon Dec 12, 2011 10:45 pm Reply with quote    Back to top    

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.

_________________
Keith Williams
keith@peacefieldinc.com
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours