IBM Support

How to define Data Source for ODBC to Oracle RAC in IBM InfoSphere Information Server

Technote (FAQ)


Question

How to define Data Source for ODBC to Oracle RAC

Answer

To configure your ODBC Data Source in .odbc.ini to support an Oracle RAC environment used for High Availability, i.e. failover, you will need to review the tnsnames.ora file to collect the SERVICE NAME and first host listed to use them in the DSN.



Example below:

Your tnsnames.ora

ORCL.WORLD =
(DESCRIPTION =
(LOAD_BALANCE = on)
(FAILOVER = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = ETL1)(PORT = 1000))
(ADDRESS = (PROTOCOL = TCP)(HOST = ETL2)(PORT = 1000))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL.world)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 80)
(DELAY = 5)
)


Your .odbc.ini

[oram10]
Driver=/IBM/Infoserver/Server/branded_odbc/lib/VMora22.so
Description=DataDirect 5.X Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=1
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=1
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName= ETL1 << first host listed
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1000
ProcedureRetResults=0
ServiceName=ORCL <<service name
UseCurrentSchema=1

You also need to update uvodbc.config file that is located under each project with the following entry:

<yourdsname>
DBMSTYPE = ODBC


Document information

More support for: InfoSphere Information Server

Software version: 8.0.1, 8.1

Operating system(s): AIX, HP-UX, Linux, Solaris

Reference #: 1404446

Modified date: 15 February 2013