Oracle 9i odbc driver configuration HP-UX ****RESOLVED****
Moderators: chulett, rschirm, roy
Oracle 9i odbc driver configuration HP-UX ****RESOLVED****
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
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Still mixing your fruits.
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.
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
"You can never have too many knives" -- Logan Nine Fingers
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?
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
"You can never have too many knives" -- Logan Nine Fingers
Never in a million years would I have guessed that the connection would be that simple.
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
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
Parameterize everything that could change from environment to environment.
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:
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.
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
Parameterize everything that could change from environment to environment.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
I will mark the thread resolved.
Thanks
Steve Hershberger
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
oracle connectivity
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
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
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.
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.
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
"You can never have too many knives" -- Logan Nine Fingers