Datastage to Oracle connectivity

udayanguha · Post by **udayanguha** » Tue Mar 15, 2016 11:51 am

Hi,
I am working in a project where the Datastage administrator doesn't know much. I am facing issues connecting to Oracle database through Datastage (through Oracle connector).
1. I have Datastage client installed on my windows machine and even have administrator access to Datastage server on UNIX.
2. I have an Oracle database to which I want to connect to.

Several articles on the internet talk about tnsora files, odbc.ini file,changes to dsenv file and various different things but I am not getting what I need to do to start things afresh.
Can anyone briefly tell me the step by step procedure to set up the connectivity. Even if I know the steps in brief, I can look up on internet about those things in detail.

Thanks in advance.

chulett · Post by **chulett** » Tue Mar 15, 2016 12:23 pm

All of the stages like that come with a pdf "Connectivity Guide" which includes the prerequisites / configuration steps you'll need. Dig that up for Oracle and go through it. High level, you'll need an Oracle client on the ETL server with DataStage configured so it knows where to find it.

ps. Got nothing to do with ODBC.

PaulVL · Post by **PaulVL** » Tue Mar 15, 2016 1:43 pm

Big hint: Be prepared to reach out to your Oracle DBA.

qt_ky · Post by **qt_ky** » Tue Mar 15, 2016 2:47 pm

Installing and configuring the Oracle Client on the DataStage server, like Craig mentioned, will let you use the Oracle Connector stage. This is generally the preferred method.

Alternatively, you can skip the Oracle Client and configure the .odbc.ini file on the DataStage server, which would let you use the ODBC Connector stage.

udayanguha · Post by **udayanguha** » Wed Mar 16, 2016 9:39 am

So I have a 64 bit client machine but Oracle client which I need to install, will that be 32 bit or 64 bit. As I heard somewhere that Datastage is a 32 bit system.
Also, after installing the client what changes do I need to make at the server side. Specifically looking for the following things:
1. Do I need to make any changes to the ODBC.ini file.
2. What changes do I need to make in PATH and LD_LIBRARY_PATH
3. Lastly, why do I need to make changes to PATH ans LD_LIBRARY_PATH through the dsenv file. Can't I simply make changes in these variables in UNIX permanently.

chulett · Post by **chulett** » Wed Mar 16, 2016 10:07 am

What do you mean by "client machine"? The Oracle client needs to be installed on the ETL server and should match the "bit-ness" of DataStage. As noted, those should all be detailed in the Configuration Guide, did you find it? They're online as well if you don't have them locally for some odd reason. I would suggest you stick to making environment settings changes to the dsenv file as they are really just for DataStage, there's no need to make them global unless a) you're sure you want to and b) are certain won't adversely affect anything else. IMHO.

And again, going native Oracle (OCI/sqlnet) has got nothing to do with ODBC.

udayanguha · Post by **udayanguha** » Wed Mar 16, 2016 11:05 am

Thanks Chulett for the reply. I found a lot of documents including the configuration guide and it has so many steps which look contradictory at many stages. I am not even able to figure out whats in scope in my case and whats not. So please bear with me on this.

Also, one more thing. Once I add the changes to dsenv file and run the dsenv file, I observed the changes are not permanent. I have to rerun the dsenv file again. Why is that so? How to make changes permanently in PATH variable.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 16, 2016 6:57 pm

Stop and start the engine, which forces the dsrpcd process to re-read the dsenv file. All child processes that it creates (i.e. requests from clients) will then carry the new values into their shells.

chulett · Post by **chulett** » Wed Mar 16, 2016 10:21 pm

Right, you don't "run" the dsenv file, DataStage does.

udayanguha · Post by **udayanguha** » Thu Mar 17, 2016 1:27 pm

Ok, So i re-ran the DS engine and ASB agent but couldn't connect even now. But my question now is, Datastage runs the dsenv file. But when I log in into Datastage UNIX server through my ID, I do not see the PATH and LD_LIBRARY_PATH as resolved into the values which are provided in dsenv file. Even the parameter DSHOME has nothing in it. But when I go to dsenv file and run it using the command . ./dsenv, then only my parameters are resolved. How come we need not run the dsenv file while running the Datastage job.

P.S. Quite new to configuration and environment set up so if my questions are really basic, please excuse me. I tried searching on it from my side as well but got more confused.

PaulVL · Post by **PaulVL** » Thu Mar 17, 2016 1:51 pm

You are talking about a basic aspect of Unix.

You need to SOURCE files (not run) in order to set your environment variables correctly for the ID you are working with.

Your desired paths/variables may be different than some other user on the box because of the nature of the work you do, or for security reasons you are part of different groups that give you access to different data.

When you log in, there is a ".profile" hidden file that typically gets sourced in order to set all of those default settings you want. (Google that since it is important for you to understand.)

If you set $DSHOME and source $DSHOME/dsenv from that file, then you will get your desired variables every time you log in.

Your admin sourced dsenv when he started the datastage engine. Every time he/she makes a modification to that file, the admin needs (should) bounce the engine for it to take effect within the application.