IBM Integration Bus v9.0/v10.0 and IBM WebSphere Message Broker v8.0 provide DataDirect ODBC Wire Protocol drivers for ODBC connectivity to Oracle database servers. Being wire protocol drivers, they can connect directly to the Oracle database server via TCPIP and do not use or require Oracle client libraries. The ODBC connection properties are defined in the $ODBCINI, as discussed in Sample WebSphere Message Broker ODBC configuration files .
However, if you already have the Oracle client libraries installed on the same box that the Mes-sage Broker / Integration Node resides on then you can make use of your existing Oracle tnsnames.ora configuration file to centralize connection information for your Oracle environment and simplify maintenance when changes occur.
DataDirect provides the ServerName connection property to be used in the odbc.ini DataSource Name stanza to refer to a net service name that exists in the tnsnames.ora file. The corresponding net service name entry in the tnsnames.ora file is used to obtain Host, Port Number, and Service Name or SID information. This option is mutually exclusive with the HostName, PortNumber, SID, and ServiceName connection options.
If the ServerName connection property is specified, then the TNS_ADMIN environment variable setting is used to obtain the tnsnames.ora file path. If you do not define the environment variable TNS_ADMIN, then the ORACLE_HOME environment variable is used.
Alternatively, the connection option TNSNamesFile can be used to specify the name and fully qualified path of the tnsnames.ora file in the DSN stanza of odbc.ini. This will override any TNS_ADMIN or ORACLE_HOME environment variable settings.
So for example, instead of specifying the Hostname, PortNumber and ServiceName in the odbc.ini DataSource Name stanza, you can use ServerName.
odbc.ini:
;# Oracle stanza
[ORACLEDB] Driver=
The net service name myOracleNetServiceName would have a definition in the Oracle client tnsnames.ora file similar to the following:
tnsnames.ora for Oracle DB:
myOracleNetServiceName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myOracleHostName)(PORT =
myOraclePortNumber))
)
(CONNECT_DATA =
(SERVICE_NAME = myOracleServiceName)
)
)
If there are multiple Oracle instances that can be connected to, for example in an Oracle Real Application Cluster (RAC) environment, then multiple Address entries can be specified in the tnsnames.ora Address List as shown below:
tnsnames.ora for Oracle RAC:
myOracleNetServiceName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HostNameA)(PORT = PortA))
(ADDRESS = (PROTOCOL = TCP)(HOST = HostNameB)(PORT = PortB))
(ADDRESS = (PROTOCOL = TCP)(HOST = HostNameC)(PORT = PortC))
)
(CONNECT_DATA =
(SERVICE_NAME = myOracleServiceName)
)
)
where HostNameA, PortA, HostNameB, PortB and HostNameC, PortC are the hostnames and port numbers of the different nodes in Oracle RAC.
Client connect time failover is supported, but not Transparent Application Failover. This means that the FAILOVER_MODE parameter for Oracle Net is not supported.
We hope the above steps will help you to correctly configure your ODBC connectivity with the Oracle database using TNS. Please let us know if there are any questions.
Authors:
Laurence Hook (IIB L3 support)
Paul Faulkner (IIB L2 support)
Thomas Bien (IIB L2 support)
Vivek Grover (IIB L2 support)