Oracle 9i odbc driver configuration HP-UX ****RESOLVED****

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Oracle 9i odbc driver configuration HP-UX ****RESOLVED****

Post by inter5566 »

Good morning all,

I seem to be having problems configuring DS7.1r1 on HP-UX 11.11 to use the Oracle 9i plug-in. I have ORACLE_HOME pointed to what I believe to be the correct directory. And have $ORACLE_HOME/lib32 included in SHLIB_PATH. When I try to use the DSN within UV with DS_CONNECT I get an error message saying it can't find libwtc8.sl. This is confusing because this is a shared library within the Oracle 8.0 lib directory :?

Does any of this strike a key with anyone?

Thanks,
Steve
Last edited by inter5566 on Thu Jun 24, 2004 1:46 pm, edited 1 time in total.
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You seem to mixing apples and oranges. :? What you are setting up via 'dsenv' is for use by OCI not ODBC.

If you've correctly set the $ORACLE_HOME and $SHLIB_PATH environment variables in dsenv then restarted DataStage, you should be fine. Try to import metadata using the OCI9 plugin, or drop one in a job and see if you can 'View Date' using it.

Don't worry about the DS_CONNECT problem until you actually get ODBC setup. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Post by inter5566 »

Craig,

I tried the metadata import with OCI9I in manager, but the DSN pull down box did not list the DSN I setup in the .odbc.ini file. Do you know where the DSN drop down box gets populated from? There is some mention of sql*net in the DS plug-in Install and Config pdf, is this part of UNIX or part of Oracle?

Thanks,
Steve
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Still mixing your fruits. :wink:

The ".odbc.ini" file has nothing to do with setting up OCI. SQL*Net is part of Oracle and you must have (at the very least) an Oracle Client (and thus SQL*Net) installed on the same server that DataStage lives on.

I'm assuming you do and that is what $ORACLE_HOME is pointing to. Unless $TNS_ADMIN is set (or you are using ONS) and things are not in their 'normal' places, you should be able to navigate to $ORACLE_HOME/network/admin on your server and find a file called "tnsnames.ora". That is where you'll find a list of the "DSN" names you can use. If this doesn't make sense or you can't find what I'm talking about, get your DBA involved and find out what's what and what's where.

I've seen times where the drop-down box doesn't populate correctly, but at worst case you can just type a DSN name in the box for it to use. As above, however, if it doesn't exist in the tnsnames file, you won't be able to connect correctly.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Post by inter5566 »

Craig,

Okay. Now where do the Oracle and Oracle wired drivers within the .obbc.ini file come into play? I feel like I'm back in 1st grade here :( .

Steve
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You would use those if you actually wanted to access Oracle via ODBC. My general Rule Of Thumb is to use a "native" access method (like OCI for Oracle) over a more "generic" method (like ODBC) if at all possible.

The standard Oracle ODBC driver would still require SQL*Net in order to operate, whereas wire drivers do not require any kind of a 'client' loaded.

For example, I use a wire driver over ODBC to access SQL Server. This gets me in without having to have anything SQL Server related loaded on my DataStage server.

Does that help?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Post by inter5566 »

Yes, that helps. We are an Informix shop and I'm used to having to have an entry in .odbc.ini for any connection to Informix databases. So in practice it is possible to access Oracle 9i, both local and remote, without any entry for oracle in either .odbc.ini or uvodbc.config?

Steve
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Correct!
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Post by inter5566 »

Never in a million years would I have guessed that the connection would be that simple. :o :lol: :P

Any suggestions for eliminating the requirement of fully qualified path names to tables on remote servers. Within our environments the development owner is different from the production owner. Therefore causing complications with migrating code. Can a variable be added to my entry in the tnsnames.ora file to resolve the path?

Thanks so much for the help today Craig :D
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Parameterize everything that could change from environment to environment. :wink:

At a minimum you should need three Job Parameters for Oracle: DSN, User ID and Password. It sounds like you should add a fourth - Owner or Schema Name. Then in the OCI stage, simply reference the Job Parameter in the sql.

For example, when using "Column Generated SQL" (do *not* use the "Fully Generated" option, it is evil), when you click on "Build" there is a place for tablename(s), in essence your "From" clause. Use something like:

Code: Select all

#OWNER#.TABLENAME
there, then all that is required for your migration is a change to the parameter values.

Parameterize everything that could change from environment to environment. :lol:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
inter5566
Premium Member
Premium Member
Posts: 57
Joined: Tue Jun 10, 2003 1:51 pm
Location: US - Midwest

Post by inter5566 »

I agree 200% with "Parameterize everything". I hadn't played with the stage enough yet to realize the difference between fully generated and column generated. My DBA group is researching use of environment variables and tnsnames.ora file entries to help resolve the issue. Luckily we have a few months to work out the basic structure of our standards for DataStage Oracle connectivity.

I will mark the thread resolved.

Thanks
Steve Hershberger
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Does Oracle still use the word "easy" in the SQL*NET configuration tool? I always felt this was false advertising.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
lax1996
Participant
Posts: 3
Joined: Tue Feb 17, 2004 10:44 am

oracle connectivity

Post by lax1996 »

Even I have the same problem of looking the DSN names from the drop down box.

I installed oracle client on datastage server and modified the tnsnames.ora and alos changed from 64 bit to 32 bit. Now the thing happens and able to connect from Unix prompt , But the thing appears that I am unable to import metadata from datastage Manager. When I tried to import from datastage desinger I dont find my DSN names under the drop down box.

Please suggest me

Thanx
User name lax1996
inter5566 wrote:Craig,

I tried the metadata import with OCI9I in manager, but the DSN pull down box did not list the DSN I setup in the .odbc.ini file. Do you know where the DSN drop down box gets populated from? There is some mention of sql*net in the DS plug-in Install and Config pdf, is this part of UNIX or part of Oracle?

Thanks,
Steve
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

To elaborate on what I said earlier... depending on how, exectly, your tnsnames.ora file was created, DataStage may or may not be able to parse the names out of it correctly. This, in my experience, happens when you manually edit the file or make it look like anything other than exactly what DataStage is expecting it to look like. Whatever that is. :lol:

Regardless, you can always simply type a known good name in the box instead of relying on the drop-down list.

And yes Ray, AFAIK, it is still called the "Easy Configurator" or some such. I don't use it.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”