Page 1 of 2
Database stage for Oracle 10g
Posted: Tue Mar 13, 2007 2:46 pm
by vnspn
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.
Posted: Tue Mar 13, 2007 2:57 pm
by chulett
Yes - OCI9 and OCI Load both, with a 9i client. They don't 'officially' support 10g until 7.5.x from what I recall.
Posted: Tue Mar 13, 2007 3:02 pm
by vnspn
Craig,
What do you mean by "with a 9i client"? Do you mean to say 9i client should be in the DS Server?
Thanks.
Posted: Tue Mar 13, 2007 3:04 pm
by chulett
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.
Posted: Wed Mar 14, 2007 7:04 am
by vnspn
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?
Posted: Wed Mar 14, 2007 7:07 am
by chulett
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.
Posted: Wed Mar 21, 2007 11:14 am
by vnspn
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?
Posted: Wed Mar 21, 2007 11:31 am
by chulett
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.
Posted: Wed Mar 21, 2007 11:43 am
by vnspn
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.
Posted: Wed Mar 21, 2007 12:00 pm
by chulett
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.
Posted: Wed Mar 21, 2007 1:13 pm
by vnspn
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.
Posted: Wed Mar 21, 2007 1:26 pm
by chulett
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.
Posted: Wed Mar 21, 2007 2:03 pm
by kcbland
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
Posted: Wed Mar 21, 2007 2:47 pm
by vnspn
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?
Posted: Wed Mar 21, 2007 4:04 pm
by kcbland
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.