DSXchange

Dear all,

What is the advantage/disadvantage of using just 1 OCI stage for input/output to/from one database?

Let's say I'm only accessing one database in one job. But I have to read 4 tables and write to 2 tables in that 1 database. Will it be better if I only use 1 OCI stage with many links?

Kindly advise...


Regards

ND

Couple of reasons...

More than likely in your example, the minimum number of OCI stages would be two - one reader and one writer. Your other option would be to use six stages. You would want to consolidate down to two, primarily to minimize the number of connections to your database. Perhaps those kind of resources aren't an issue for you, but we strive to minimize them as much as possible. From a writing standpoint, besides also minimizing connections, using a single stage allows you to do 'transaction grouping' where you could setup an 'all or nothing' scenario. Hope this helps,

-craig

ps. Not sure there is a 'disadvantage', per se.

Craig,

I agree with you. Previously I use so many OCI stages just to open different tables from one database. And I keep getting this 'Abnormal Termination' message. But after I redesign the whole job to minimise the number of OCI stages, it works fine and faster. I have to change my other jobs to minimize the number of OCI stages too.

But, I can't find where the 'Transaction Grouping' option. Is there any option that I should Enabled? I am using OCI ver.4 and Oracle 9i on Sun.

Kindly advise...


Regards

ND

If you have multiple input links to an OCI stage, then on the 'Input' tab under the 'Transaction Handling' sub-tab, a new option should be available - 'Enable transaction grouping'. Note that it is only available when multiple links exist.

Once you enable it, a list of the links will appear and you can then control the behaviour of each 'On Skip' & 'On Fail', which can be set to either 'Continue' or 'Rollback'.

-craig

Thanks Craig,
It works Fine now...




Regards

ND