Oracle 10g with DS 7.5.1

[deployDS]

Hi all,
I don't know if this issue came for discussion in this forum, my search resulted in different topics.
We recently installed DS7.5.1 on a new HPUX server as our current current server which holds DEV, SIT, UAT, PROD and 10 other projects became more conjested. Our target Db is Oracle10g. Our old box has Oracle 9i client and DS was installed without NLS. For some reason, they installed NLS on the new box and Oracle10g client along with it.
Today I tried run a server job(using oracle OCI stage) and a paralel job (using oracle ent stage) to test oracle read and it failed with the following error for parallel job:

Code: Select all

Ora_TABLEX: connect failed for  env: abcd, user: xyz, password: *****
Oracle error: -12154
Error while trying to retrieve text for error ORA-12154

and the same error even for the server jobs. I tried to execute the same query from command line using sqlplus and it worked fine. I tried to import metadata definitions by connecting to the database. I tried using the plugin metadata definitions and OracleOCI9 drivers. But none of the databases were visible to me. When I manually entered the dbname, user and password, it resulted in the same error message as the job.

Code: Select all

Error while trying to retrieve text for error ORA-12154

Then I tried importing using ODBC table definitions and it worked for the same database without any problem. I included the TNS_ADMIN in the dsenv file and restarted the datastage engine and tried to run the job. got the same error message.
I checked the ORACLE_HOME and TNS_ADMIN variable values and every thing was good. Initially we used to get an error "unable to load driver libclntsh.so.9.0"(Not the exact error msg). We then created a soft link

Code: Select all

libclntsh.so.9.0 -> $ORACLE_HOME/lib/libclntsh.so.10.1

But i'm unable to read data from Oracle OCI stage or Oracle Ent stage. I don't know if i'm missing something to check.
I have one more question:
Does Oracle 10g client work with DS 7.5.1? Or do we need to have 9i client installed to access Oracle 10g server?

Thanks in advance!!

[chulett]

Yes, the 10g client can work fine. Need to know what HPUX server you are running, though... Itanium or PA-RISC? Running a 32bit or a 64bit client? And you shouldn't need to set TNS_ADMIN unless your tnsnames file is in a non-standard location, is it?

[deployDS]

model says "ia64 hp server rx2600".
I assume its a 64 bit Itanium. Please correct me if I'm wrong. I'm running a 64bit client.
And the tnsnames file in the standard location.
$ORACLE_HOME/network/admin
so, I removed this entry in dsenv and restarted the dsengine and tested the job. No luck.
I don't know if this causes any issue, but NLS was enabled on this box. and I get a warning regarding the NLS each time I run the job before error appears.

Code: Select all

main_program: The NLS character map <ISO-8859-1> is specified, but NLS_LANG is not set;
the NLS character map must be set to an ICU codepage which is equivalent to
the character set specified by NLS_LANG.

does this make any difference?

[chulett]

Yes, Itanium. You got a 10g client because there's no 9i one for them. Change your shared library path in dsenv to point to the 32bit Oracle libraries rather than the 64bit ones, see if that helps.

[deployDS]

Thanks craig. The problem got resolved. But the error was a different though. After changing the oracle library path to 32 bit libraries, I tried the job and it again aborted with same error code. I went to look at the environment variable initialized in the job log and found nothing different than the previous entry. And one more thing is ORACLE_HOME is not there in the variables list. I added the ORACLE_HOME and ran the job and the job finished successfully. Datastage was unable to read the changed dsenv file. We figured that improper shut down of the datastage caused this issue. There were some shared segments owned by "root" user and dsadm was unable to stop the dsengine because of that error. We asked "root" user to restart the dsengine and then it worked fine. As there is some update running on the new server, i was unable to check with the 64 bit libraries, but the jobs are running fine with the 32 bit libraries.

Thankyou very much for your help.

[chulett]

You're welcome and don't worry about the 64bit libraries as DataStage cannot use them.