Connecting different oracle database at a time

shyju · Post by **shyju** » Sun Dec 25, 2005 10:14 pm

Hi All,

Can we connect two different oracle databases to a particular DataStage project at a time? If so then how can this be acheived?

Thanks,
Shyju.

kcbland · Post by **kcbland** » Sun Dec 25, 2005 10:49 pm

Use OCI stages, which use the SQLnet Oracle client. Make sure the DBA's install the client on the Unix server, put ORACLE_HOME and the shared library paths in the dsenv file, recycle DS services, and you're all set. DS resolves thru $ORACLE_HOME/network/admin/tnsnames.ora file for all connections, so DON'T put a ORACLE_SID in the dsenv file.

The only issue is the Oracle compatibilities between versions of the database and versions of the client. Oracle 9i client works with 8,9 and 10 databases, so that's usually a great choice in the mixed environment. You'll just use the Oracle OCI9 stage in DS version 6, which was renamed to just OCI in DS 7 when support dropped for Oracle 7 plugin.

Of course, you can always use ODBC drivers, but that is really limiting.

shyju · Post by **shyju** » Sun Dec 25, 2005 11:30 pm

Hi kcbland,

Please correct me if i understood wrongly. The $ORACLE_HOME/network/admin/tnsnames.ora contains many database connections irrespective of which DS project we are using.

The question is how will the jobs in a particula DS project understand to what Database it is connected to even by using an OCI Stage?

Should we mention various parameters in the Administrator for each database that we connect to and use these parameters in the jobs so that we can connect to different database in a same job?

Bala R · Post by **Bala R** » Mon Dec 26, 2005 1:23 am

Hi shyju,
You have answered your question!. Make the parameters required for database connectivity as job parameters for e.g. in OCI stage the database name, UserName and password. Discussion on setting this job parameters as project specific parameters and more is in FAQ posted by Vincent.
HTH

kcbland · Post by **kcbland** » Mon Dec 26, 2005 8:31 am

Projects don't connect to a database, jobs connect. In one job, you could have different OCI stages talking to different instances, all within the same job. The only requirement is that all Oracle stages must use the same Oracle client installation, which is configured in the dsenv file and applies to all jobs in all projects because the DS engine is started with the environment values in the dsenv file.

As for each OCI stage, it takes a DSN name, which is just the name of the entry in the tnsnames.ora file for the instance you wish to connect. Using parameters instead of hard-coded literals is a wise decision.