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.
Connecting different oracle database at a time
Moderators: chulett, rschirm, roy
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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?
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?
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle