Problem when calling SP stage

[evee1]

I'm struggling a bit to get the connectivity to the DB in my environement.
It's a new project, no "real" Datastage environement yet (it's coming soon), but there is an existing Oracle database on the UNIX server.
So in the meantime I have uploaded an image of VM with a Datastage server onto my laptop (I've already had DS clients installed). Now I'm trying to use this temp environement to build and test some very simple job stubs. One of them is a call to a SP that exists in the Oracle DB on the UNIX server.

So far I have set up he following:
/opt/IBM/InformationServer/Server/DSEngine/.odbc.ini
/opt/IBM/InformationServer/Server/DSEngine/dsenv
/opt/IBM/InformationServer/Server/Projects/dstage1/uvodbc.config
I also have an Oracle clients installed on VM and my latop
tnsnames.ora on both DS server and my laptop contains the database entry and dsenv sets TNS_ADMIN to its location.

I can successfully connect to the Oracle database on the UNIX server from the DataStage server on VM using sqlplus.
I can also successfully connect to this DB from an Oracle Connector (using Test), however I had to pass the DB password manually (somehow it's not picking it up from the parameter set that I defined using the Administrator. but's it's a separate issue).

I have a job that calls an SP, but when I run it I'm getting the following error:
Stored_Procedure_Test1,0: Error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Stored_Procedure_Test1,0: Error occurred in call to ORPHCallActivePluginInitialize().

I checked the job log and the environment variables seem to be correct, and I have no idea what else might be wrong .

Can someone suggest what I could check next, please. I can post the contents of the configs if someone wishes to have a look.

Thanks!

[evee1]

Forgot to mention that I'm also getting the error ORA-12154: TNS:could not resolve service name when trying to import metadata via ODBC connection.

[ray.wurlod]

Looks like an incomplete entry in tnsnames.ora file - one (at least) of the configurations lacks a SERVICE_NAME property (in the CONNECT_DATA sectioon) but requires one.

[evee1]

If tnsnames.ora is incorrect should I be able to tnsping to the SERVICE_NAME? I can, both from the DS server (VM) and my client machines.

[ray.wurlod]

Is ORACLE_HOME correctly set in dsenv? Perhaps it is using a different value from that used by your interactive user? Clutching at straws a bit here, it's difficult to diagnose these things remotely.

[evee1]

Thanks for help anyway. I'm running out of ideas here.

The environment of the server (after I run . dsenv) has the following vars:

ORACLE_SID=SVNAME
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

The TNS entry sitting in /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora is:
SVNAME=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SVNAME.full.name.qualifier)
)
)


On the client side ORACLE_HOME env var is:
ORACLE_HOME=D:\app\my.name\product\11.2.0\client_2

The TNS entry in D:\app\my.name\product\11.2.0\client_2\NETWORK\ADMIN\tnsnames.ora looks like this:
SVNAME=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB_HOST)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=SVNAME.full.name.qualifier)
)
)


Am I missing anything?

[evee1]

One other question. If I change the contents of tnsnames.ora do I need to restart the Designer, or does it reads the ora file on request?

[chulett]

If I change the contents of tnsnames.ora do I need to restart the Designer, or does it reads the ora file on request?

You don't need to restart anything after that change.

[evee1]

Played around with this for a while and still cannot find the cause of my problem.

I switched on number of reporting options hoping that it would give me more information about the actual SP call, but it did not. Maybe there is some other parameter that I should enable to give me this kind of info?
The ones I've got are:
APT_DUMP_SCORE=1
APT_MSG_FILELINE=1
APT_PM_SHOW_PIDS=1
APT_PM_SHOWRSH=1
APT_RECORD_COUNTS=1
APT_SHOW_COMPONENT_CALLS=1
APT_STARTUP_STATUS=1

The other problem is that I also cannot import metadata via ODBC Table Definitions options, getting the following error:
ORA-12154: TNS:could not resolve the connect identifier

From what I understand this type of connection uses the client ODBC connection mechanism, and this one is working fine when tested from the ODBC Data Source Administrator.
I have searched this site for that isseue, but this is problem usually has been fixed by correcting ORACLE_HOME or tnsnames.ora etc.
But in my case ORACLE_HOME is definitely correct. And tnsnames.ora as well; otherwise I should not be able to use sqlplus to connect to the database, I think.

This might be a bit farfetched theory, but could my problems be related to 32/64 incompatibility? My ODBC connections are 32bit. Could this be a problem with DS 8.5?
Also I would expect a different kind of error reported if this was the issue.

I'm close to the conbclution that my DataStage installation is not quite correct (to put it mildly) and I might need to wait for the proper one on our project machine.

[ray.wurlod]

Also look at your shared library search path. Make sure the 32-bit libraries are being found (at least for DataStage processes) - for example in dsenv make sure that lib32 occurs ahead of lib for any product.

[evee1]

Yes, I have checked that and it was also correct.
This week we have a proper Datastage environment being installed. Maybe I will have more luck there.

[evee1]

Just to close this topic off. It turned out that after I have installed FixPack 1 patch it all started to work.

[chulett]

Always good to make sure you are up-to-date on fixpacks.