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=/ODBC/V7.0/lib/UKora26.so
Description=DataDirect ODBC 7.0 Oracle Wire Protocol
ServerName=myOracleNetServiceName
TNSNamesFile=/my_oracle_installation/network/admin/tnsnames.ora
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0

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)

4 comments on"Can I use Oracle TNS service with IBM Integration Bus (IIB)?"

  1. @Waleed Abu-Yahia,
    Oracle RAC with TNS via JDBC is not configured “out of the box” with IIB.
    The JDBC Configurable Services in IIB use a connection string template.
    However, it is possible to override this template to ensure the connectivity you desire.
    Oracle RAC connectivity via JDBC is documented in this technote:
    http://www-01.ibm.com/support/docview.wss?uid=swg21587156

  2. Waleed Abu-Yahia July 31, 2017

    Hi Vivekgrover,

    how can i use TNS with JDBC connection where oracle database on RAC?

  3. Dear Vivek and team,

    kindly let us know if IIB 9 is well supported on 12C database on both the database types CDB vs.PDB

    Regards
    Anis

  4. K5W2_Anisur_Rehman October 08, 2015

    Dear Vivek and team,

    I truly appreciate your time and effort making your recommendation and clarity on ODBC topic.

    Regards
    Anis

Join The Discussion

Your email address will not be published. Required fields are marked *