Problem when calling SP stage
Moderators: chulett, rschirm, roy
Problem when calling SP stage
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!
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!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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?
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.