DSXchange

I am trying to establish an ODBC connection for my ORACLE RAC database to import metadata using table definitions of stored procedure.

Here is my tnsnames entry:
test.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = test1)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = test2)
(PORT = 1521)
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)


How should be my odbc.ini file entry for datastage to connect for ODBC connection?

Any help is appreciated.

Thanks.

For connection via odbc entry is required in .odbc.ini
For connectino via oracle enterprise stage entry is not required in .odbc.ini
But the userid used to connect to oracle should have dba level priviliges


Regards
Sreeni

I agree with you, i need a sample odbc.ini entry for Oracle RAC in this scenario.

Nothing magical about RAC, your 'samples' are in the .odbc.ini file in $DSHOME like normal. Meaning, treat it as any other ODBC Oracle data source.

Criag, when i use the following entry it is failing with an error:

TNS:listener could not resolve SID given in connect descriptor

ODBC.ini file entry:
[testodbc]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMora23.so
Description=DataDirect Oracle Wire Protocol driver
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=test1
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521
ProcedureRetResults=0
SERVICE NAME = test.world
UseCurrentSchema=1


Do we need to put two odbc entries because it has two host names in TNS entry?

You may have two host names for the load balancing but you still have only a single tnsnames entry and that's all you need at any given time. Have you been able to use this tnsnames entry outside of DataStage? Meaning, can you make a sqlplus connection to it to the RAC database?

YEs, I am able to connect to this RAC from UNIX box using SQLPLUS.

OK, that's good. Now when you say you are "using it" what exactly does that mean? Is this via an ODBC stage in a job or elsewhere? If elsewhere, have you established whether you can or cannot connect using it in an ODBC stage in a job?

The more details you provide about what you are doing, the better.

I am using the ODBC Connection by creating a DSN entry and using that DNS entry for Importing the table definitions for Stored procedures.

Any thoughts?

I've already posted my thoughts.

chulett wrote:have you established whether you can or cannot connect using it in an ODBC stage in a job?

The more details you provide about what you are doing, the better.

Show us your DSN definition from uvodbc.config

Here is my uvodbc.config file contents:

[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1

<testodbc>
DBMSTYPE = ODBC