Database stage for Oracle 10g

vnspn · Post by **vnspn** » Tue Mar 13, 2007 2:46 pm

Hi,

We are working in DS version 7.1 and the database used is Oracle 10g.

But I only have these 4 Oracle stages in my palette -
Oracle 7 Load
Oracle OCI 8i
Oracle OCI 9i
Oracle OCI Load

Can we use 'Oracle 9i' stage to read and write data to an Oracle 10g database?

Thanks.

chulett · Post by **chulett** » Tue Mar 13, 2007 2:57 pm

Yes - OCI9 and OCI Load both, with a 9i client. They don't 'officially' support 10g until 7.5.x from what I recall.

vnspn · Post by **vnspn** » Tue Mar 13, 2007 3:02 pm

Craig,

What do you mean by "with a 9i client"? Do you mean to say 9i client should be in the DS Server?

Thanks.

chulett · Post by **chulett** » Tue Mar 13, 2007 3:04 pm

Exactly. The 9i Client software should be installed and configured on the DataStage server and then you can use that to connect to any 10g instance.

vnspn · Post by **vnspn** » Wed Mar 14, 2007 7:04 am

Oh! So mean that using the Oracle 9i Client, I can connect to the 10g Server.

Currently I have only Oracle 10g Client in my DS Server. So I have to uninstall 10g Client and install Oracle 9i Client?

chulett · Post by **chulett** » Wed Mar 14, 2007 7:07 am

No, don't uninstall anything - just install the 9i client as well. Then you'll need to configure DataStage so that it uses it. I know how to do that for a UNIX server, but not a Windows server.

vnspn · Post by **vnspn** » Wed Mar 21, 2007 11:14 am

I already have an ODBC connection to connect from the 10g Client to the 10g database server. Is it not possible to use this ODBC connection in the Oracle OCI 9i stage?

chulett · Post by **chulett** » Wed Mar 21, 2007 11:31 am

No, the 'OCI' stages leverage the client directly and have nothing to do with ODBC. Now, you could use the ODBC stage (or the DRS in ODBC mode) to use that connection, but you are much better off getting the 'native' connectivity working. IMHO.

vnspn · Post by **vnspn** » Wed Mar 21, 2007 11:43 am

By "ODBC connection", I mean the connection name that we give under the "Database source name" in the OCI stage properties. My question was to create this "connection", is it fine if this connection was created using the 10g Client (or) is this connection itself should be created only using the 9i Client (because the OCI 9i stage is going to use it)?

Sorry if I'm not clear in explaining it.

chulett · Post by **chulett** » Wed Mar 21, 2007 12:00 pm

So, by "connection" you must mean the tnsnames.ora file entry aka the Data Source Name. That's not client specific, per se. It's just a text file that generally lives in the $ORACLE_HOME/network/admin directory of the current client.

If you are using the 9i client then you'd need to ensure the connection you are referring to is available in the tnsnames file under the 9i client, not the one under the 10g client.

Of course, like most things, it can be more complicated than that. You may have a 'central' location for tns entry and should be using the TNS_ADMIN environment variable to point to it. Sounds like you may need to have a chat with your DBA to get this all straightened out.

vnspn · Post by **vnspn** » Wed Mar 21, 2007 1:13 pm

Oh, yes! You are correct. I meant the DSN.

So, you mean that, if I create the DSN through "Administrative Tools" option in "Control Panel", I would not need to do (or specify) anything about the version - 9i or 10g. After creating this DSN, I could go ahead and use this DSN in my OCI 9i stages properties to connect to the specified database. Hope things are right now.

chulett · Post by **chulett** » Wed Mar 21, 2007 1:26 pm

I'm not sure. I'm familiar with how you'd do all this on a UNIX server but have never had the pleasure of working with DataStage on Windows. So some other kind soul will need to step in and help with that aspect of this issue, before I lead you too far astray.

kcbland · Post by **kcbland** » Wed Mar 21, 2007 2:03 pm

no no no, go to wherever you installed the Oracle Client 9i on the Windoze server, look under the network\admin folder and update the tnsnames.ora file there. Sorry about the 10g client support, but I don't think it works with the OCI stages in 7.5

vnspn · Post by **vnspn** » Wed Mar 21, 2007 2:47 pm

Ken,

Ok, I'm currently using DS version 7.1.

As per the steps that you have mentioned, I would need to the update the tnsnames.ora file of the 9i Client. Then I would make use of that in the Oracle OCI 9i stage properties (to connect to the required 10g database). Am I right?

kcbland · Post by **kcbland** » Wed Mar 21, 2007 4:04 pm

Yes, the OCI stage is the Oracle 9 stage on version 7.1. Install the Oracle 9i client on the DS server, setup the tnsnames.ora file on that server, and it then try to use the DS Manager to import plugin metadata. Choose the Oracle 9 plugin, you should see the list of SIDs declared in the tnsnames.ora file. If you get an error, make sure that the permissions on the Oracle 9i client folder allow all users on the server to use it.