ORAOCIBL STAGE

Smeitei · Post by **Smeitei** » Tue Mar 06, 2007 5:47 pm

My Database(redbrick) reside in - M1 server
Datastage reside in - M2 server
Oracle reside in - M3 server

#1Now i want to run DS job in M2 collect data from M1 and load it into Oracle residing in M3. Is it possible to do it using ORAOCIBL STAGE in automatic mode. If not what are the options

#2Imagine if my job is going to run every weekend .Is the functionality or ability to do the multiple update types such as insert/update, update/insert, replace etc. still exist as in normal standard OCI stage .

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 6:27 pm

Its very well possible. The basic need to ETL is to intergreate data available at different system.
All the options avaialable in OCI should funciton as normal as this scenario is concern.

chulett · Post by **chulett** » Tue Mar 06, 2007 6:28 pm

#1. Without knowing your specifics, a qualified Yes.

#2. No. That is a bulk loader stage, not OCI, so it only does inserts.

That doesn't mean you can't use it however. You could leverage Oracle by bulk loading your changes into a work table and then issuing a MERGE command. Talk to your DBA to see how to do that in your version of Oracle.

Smeitei · Post by **Smeitei** » Tue Mar 06, 2007 9:59 pm

I am confused..

I have 3 plug ins
1. ORAOCI9
2. ORAOCIBL
3. ORABULK

This is what i tried out and people suggested
1.From previous discussion i got that ORABULK is not supported for oracle 9i.
2.But while trying to use ORAOCIBL i was suggested that this stage will not work if DS and oracle reside in different server as in my case.

Is the assumption and suggestion i got correct???
which stage does only insert? ORAOCIBL or ORABULK.

My requirement is to have a weekly process which can do insert/update. I thought i will try out with some bulk load stage rather then conventional ORAOCI9 stage. Please pass on your comments.

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 10:31 pm

ORAOCI Load stage can be used for both local as well as remote load.
Pls refer the Plug in document.
You need to have the oracle client to be configured in the datastage server to connect the remote Oracle server.

chulett · Post by **chulett** » Wed Mar 07, 2007 8:11 am

Smeitei wrote:2.But while trying to use ORAOCIBL i was suggested that this stage will not work if DS and oracle reside in different server as in my case.

We know the documentation says this, but it is not true.

Any stage that leverages the Oracle 'bulk loader' - aka sqlldr - only does inserts. I've already passed on my comments. Did you chat with your DBA about what you are trying to do as I suggested?