Can ODBC stage do all, what Oracle EE stage does.

dsedi · Post by **dsedi** » Wed Aug 22, 2007 11:53 am

Hi Gurus

I am wondering if, I should use ODBC stage or the Oracle Enterprise stage for db connection. ODBC is generic stage, while Oracle EE is a native stage having native drivers which may give an edge for performance.

But is this the only difference or, do we have additional features which are not in ODBC stage

Thanks
Dsedi

dsedi · Post by **dsedi** » Wed Aug 22, 2007 1:13 pm

Thanks for the reply.
Could you please let me know

In case Oracle is on the same server as DS, then Oracle Enterprise is better than ODBC.

Can you explain me how and in what sense. Will it be better in case of performance w.r.t Select or for all DMLs.

In case Oracle in not on the same server then, which one to choose

Can I replace Oracle enterprise stage with Odbc enterprise stage and see no difference as far as performance or functionality is concerned.

Thanks

kris · Post by **kris** » Wed Aug 22, 2007 2:05 pm

I don't understand why one would even consider using ODBC.

No matter where your Database is(same box or remote), communication between DataStage and Database using Native Plug-ins is far better than ODBC. (Period!).

dsedi · Post by **dsedi** » Wed Aug 22, 2007 2:11 pm

kris wrote:I don't understand why one would even consider using ODBC.

No matter where your Database is(same box or remote), communication between DataStage and Database using Native Plug-ins is far better than ODBC. (Period!).

Kris, I have been using Oracle EE stage for all my past projects, but here the client has only setup ODBC stage and the driver for Oracle. They need to know how will it make difference if we go by using Oracle enterprise stage inplace of ODBC.

I am trying to find more info on what performance boost and how are we looking at in case we use native Oracle EE stage.

Ray mentioned in one of his posts that Select will not be affected, but there can be major differences in case of Inserts and updates between the two.

Thanks
dsedi

atul sharma · Post by **atul sharma** » Wed Aug 22, 2007 6:00 pm

ODBC stage and Enterprise stage have almost all functionality same in 7.5.X versions. ODBC is more generic and versatile, so why not use it inplace of Enterprise stage.

ArndW · Post by **ArndW** » Wed Aug 22, 2007 6:42 pm

Functionality notwithstanding, performance can be dramatically faster with the Enterprise stage, particularly with a partitioned database. I would not use ODBC given a choice.

atul sharma · Post by **atul sharma** » Thu Aug 23, 2007 8:59 am

Hi ArndW

Will it not make difference, if my Oracle is not on the same server where DS is. Should i not prefer ODBC in case where, Oracle is installed on a different server.

ds_is_fun · Post by **ds_is_fun** » Thu Aug 23, 2007 9:56 am

Exactly my point! Can you use ORacle EE if the ORacle server is not on the same server as DS Server?
Far as I understand -
If Oracle on different server than DS then allowable Oracle stages in DS are -
1. ODBC stage
2. Oracle OCI

If Oracle server as DS server then best practice would be to use -
ORacle EE stage.

Am I right?

kris · Post by **kris** » Thu Aug 23, 2007 11:26 am

Even in the first case where you have both on different servers, having an oracle client on the DS server and using plug-ins will give you better performance than ODBC.

atul sharma · Post by **atul sharma** » Thu Aug 23, 2007 12:04 pm

Can we configure Oracle enterprise stage for Remote server then??
Or it cannot be done as ds_is_fun says??

dsedi · Post by **dsedi** » Thu Aug 23, 2007 12:39 pm

Hi Kris

Did you or any one ever used Oracle enterprise stage to connect to oracle, when DS is on different server and Oracle is on different server.

If yes, can you tell what all is needed for configurations.

Thanks

ArndW · Post by **ArndW** » Thu Aug 23, 2007 3:13 pm

Many implementations have the database server on a different machine than the DataStage server. Of course EE can access remote Oracle servers, this capability is necessary for distributed environments.

atul sharma · Post by **atul sharma** » Thu Aug 23, 2007 3:38 pm

Thanks for your prompt response ArndW. Can you let me know how to configure it.

ArndW · Post by **ArndW** » Thu Aug 23, 2007 4:40 pm

Each installation is somewhat different, so there is little to do until you encounter a specific error. The DataStage Install and Upgrade Guide chapter on configuring and installing Oracle is a good start (pp. 6-19 "Setting up Database connectivity" -> "Oracle Databases"