OCI stage DB connections

nelc · Post by **nelc** » Thu Jul 17, 2003 8:26 pm

Hi all,

I noticed that each OCI target stage uses one DB connection. So when I need to load into 5 tables, DataStage actually gets 5 DB connections. This results in not enough connections esp when I am sharing the DB with some application developers.

Any way to use one connection for multiple stages/tables? Or rather anyway to use one OCI stage for multiple tables? Currently I use one stage for one table.

Thanks for any help rendered! [:)]

kduke · Post by **kduke** » Thu Jul 17, 2003 9:47 pm

nelc

I have not had a need to test this but if you draw all your links to the same OCI icon then they are supposed to share one connection and not use one per link. Try it.

Kim.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2003 10:54 pm

I have used multiple input links to one OCI stage, and it appeared only to use one database connection.
There was not much else happening at the time, so I can't be 100% sure; it worked so I didn't pursue it! [8D]

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

chulett · Post by **chulett** » Fri Jul 18, 2003 6:41 am

Exactly... unless you've got a buggy older version of the OCI8 plugin, simply drag multiple links to the same OCI stage. They will all share the same logon / connection. The only thing different to recognize is you'll need to setup all five by selecting their link name one-by-one from the appropriate pulldown.

The added benefit is they will all share the same unit of work (transaction) and so will be aware of what the others are doing. This helps with doing parent/child RI stuff. You also get a new option in the stage for Transaction Grouping which you may find helpful, check out the online help on that.

-craig

uneumann · Post by **uneumann** » Fri Jul 18, 2003 6:45 am

Nelc,

which DS Version did you use?

In DS 5.1A (HP-UX) each link to an OCI stage open their own session. In DS 5.1r5 and 6 all links (from the same transformer?) to an OCI stage use the same session.

Regards,
Udo

nelc · Post by **nelc** » Fri Jul 18, 2003 10:44 pm

Thanks for all the advice.
But how to insert into more than one table using one OCI stage? So far I have been using 1 table --> 1 OCI where I import the meta data columns and do the respective mapping.

vmcburney · Post by **vmcburney** » Sat Jul 19, 2003 4:54 am

It can be hard to see however when you drag two links into the same OCI stage you will have a two in one stage. When you open up the stage you will see that the input link name is in a combo box and you can choose from any of the links coming into the stage and set table and column settings for that link.

You can do this with most stages including reference and input stages. Usually it is done to prevent the job from becoming too cluttered, however in this case it has the added bonus of sharing a connection.

Vincent McBurney
Data Integration Services
www.intramatix.com