Hi,
We have an Oracle database installed on the same Linux box as the Datatstage. I'm trying to test DB connectivity from within the Datastage job while running the Datastage client on my local machine.
I CAN connect to my test database from an Oracle Connector stage (it looks like it's using TNS listener connections), but I CANNOT connect from the ODBC Connector, nor from the Oracle Enterprise stage.
When connecting via the ODBC Connector I'm getting the following error:
ODBC function "SQLConnect" reported: SQLSTATE = 08001: Native Error Code = 12,504: Msg = [IBM(DataDirect OEM)][ODBC Oracle driver][Oracle]ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
However, I can successfully connect to the database using sqlplus from both the server and my local client.
Does anyone has a suggestion what I am missing in my setup (below)? In the past I could successfully connect using ODBC connector when I was using a different database on a different VM (of course I have since updated the connection details in various config files).
And another question of a more generic nature - if I update .odbc.ini file, do I need to restart the DSEngine (in this case I did)?
My setup is as follows:
1. The /opt/IBM/InformationServer/Server/DSEngine/.odbc.ini file (I understand that ODBC Connector stage uses this as a source of connections) contains the following entry for my test database:
[ODBC Data Sources]
TEST_DB=DataDirect Oracle Driver
[TEST_DB]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMor824.so
Description=DataDirect Oracle driver
ApplicationUsingThreads=1
ArraySize=60000
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
OptimizeLongPerformance=0
Password=
ProcedureRetResults=0
ServerName=<Datastage_Server_IP_address>
UseCurrentSchema=1
2. The /opt/IBM/InformationServer/Server/DSEngine/dsenv contains the SID definition:
ORACLE_SID=TEST_DB
3. I have modified the /opt/IBM/InformationServer/Server/Projects/dstage1/uvodbc.config by adding the TEST_DB ODBC entry:
<TEST_DB>
DBMSTYPE = ODBC
4. The contents of tnsnames.ora on server box:
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = v2)(PORT = 1521))
)
TEST_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = v2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST_DB)
))
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = TEST_DB)
(PRESENTATION = RO)
))
5. The contents of listener.ora on server box:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST_DB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
))
LISTENER_TEST_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = v2)(PORT = 1521))
)
LOCAL_LISTENER=LISTENER_TEST_DB
6. The contents of tnsnames.ora on client box:
TEST_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = v2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST_DB)
))
7. The contents of listener.ora on client box:
SID_LISTLISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = TEST_DB)
(ORACLE_HOME = D:\app\myuser\product\11.2.0\client_1)
# (PROGRAM = extproc)
))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = artemis)(PORT = 1521))
))
ADR_BASE_LISTENER = D:\app\myuser
DEFAULT_SERVICE_LISTENER = (LISTENER)
I will appreciate any suggestions. Thanks.
Issue with connecting to Oracle via ODBC Connector stage
Moderators: chulett, rschirm, roy