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
Oracle OCI Stage Commit for UPsert
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3
- Joined: Mon Oct 19, 2009 8:51 am
- Location: asd
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.
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
"You can never have too many knives" -- Logan Nine Fingers
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?
Has anyone found a way to implement this?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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
keith@peacefieldinc.com