DSXchange

Does anyone know if it is possible to use both the Oracle 8i and the Oracle 9i clients on the same server in one DataStage project? And how that would be done?

The Technical bulletins for the ORAOCI8 and ORAOCI9 (now ORAOCI) plug-in stages says that these two stages cannot be mixed in the same job, implying that they could be mixed in two different jobs in the same project. But, as far as I know, the question of which client software gets used is determined by the setting of ORACLE_HOME, which is done in the DSENV file. So, how could you switch between two ORACLE_HOMEs?

Must be something simple I'm missing, but I'll be gosh darned if I can see it.

Larry

lebos wrote:Does anyone know if it is possible to use both the Oracle 8i and the Oracle 9i clients on the same server in one DataStage project? And how that would be done?

Nope, can't be done.

Sure, you can use the OCI8 plugin in one job and the OCI9 plugin in another, but they both would use the same (single) client.

From an oracle point of view you can install both the oracle clients on the same server.

From DataStage you would have to install the OCI8 into one project and OCI9 into another project. Then set the ORACLE_HOME env values in the administrator as user defined values. These will then be applied per project, rather then the dsenv file which sets things on a per server bases.

Interesting... hadn't thought of doing things that way to use different clients per project - but then I've never had a need to do so.

Still, the answer to the original question is "no".

We use an 8i client to update 9i tables. I have not tried a 9i client with 8i tables but I expect it to work. For testing purposes I would like to know that you could use 2 different clients with separate projects the way Eric describes. Post your solution and explain how well it works when you are done. Very clever Eric.

I just wanted to elaborate....

1. Overriding the dsenv ORACLE_HOME in a User-Defined variable within Administrator is fine for Parallel Jobs but does not work for Server Jobs.

2. Another alternative is to change the dsenv ORACLE_HOME to point to a Oracle 8.1.7 client vs. a 9.2.0 client. This way the Oracle 8.1.7 client can support Oracle 7.3.4, Oracle 8.x and Oracle 9.2.0.

1. If you remove the ORACLE_HOME and LIBPATH (or whatever fits your OS) seeting from dsenv and then apply within Administration through environment variables "per Project" ....then each Project can define what LIBPATH and ORACLE_HOME please them. On Project 8.1.7 and another 9.2.0. The dsenv settings disallow Administrator to override which is why this has to be done....but if its Parallel Jobs its different.

2. If you insist, for example, keeping ORACLE_HOME in dsenv set to 9.2.0 then for 8.1.7 use the Oracle non wired ODBC connection vs. Oracle OCI stage and this is another way. But many companies hate ODBC due to security issues and Sarbanes-Oxley.

trokosz wrote: But many companies hate ODBC due to security issues and Sarbanes-Oxley.

Security, fine, but Sarbanes-Oxley? How does that come in play here with ODBC?

Sarbanes-Oxley does not consider ODBC to be providing a secure environment. It's a interpretation that the top 3 accounting firms are using duing audits.

Wooooooooooookay.

*chuckles* And I thought it was a nice way to say, "upgrade! Avoid the evil feds!"

trokosz wrote:Sarbanes-Oxley does not consider ODBC to be providing a secure environment. It's a interpretation that the top 3 accounting firms are using duing audits.

ODBC, like OCI, is a protocol, like many others.
If you want security, put a secure layer around it.