DSXchange

Dear All,

I would like to understand how the transaction handling of database tables of a job can be handled.

For exapmple, I have a job with n number of oracle stages in it to which inserts/updates will happen. Can I set some interval to commit the data into database tables at the job level? Or is this commit specific to each oracle stage being used?
When the job runs and aborts, what will be the state of the oracle stages that are run before aborting the job? Does it rollback?
Is there any way to handle all oracle stages of a job like a transaction at Database? (commit all or none)

Thanks and regards,
Srinivas.

First off, you do need to set the Transaction Sizes in each OCI stage. There's no way that I am aware of to set it at the job level or to pass it in as a parameter.

When a job aborts, commit handling really isn't any different than with any other sql product... any uncommitted changes are rolled back. Now, there are games you can play with LogMiner and such, but from your standpoint all you will have in the target is thru the last commit.

When you have multiple targets, you can use something called 'Transaction Grouping' to get your all-or-nothing result. There are prerequisites: you much use a single OCI stage (so all work is done via the same connection) and all links must come from a single Transform. When that happens, a new checkbox for Transaction Grouping will appear on the Transaction Handling tab, and you'll have to define the rules under which it operates. See the online help for details.

-craig