11 Configuring Oracle Database Gateway for ODBC

Create the Initialization Parameter File

You must create an initialization file for your Oracle Database Gateway for ODBC. Oracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the $ORACLE_HOME/hs/admin directory.

To create an initialization file for the ODBC gateway, copy the sample initialization file and rename it to initsid.ora, where sid is the system identifier (SID) you want to use for the instance of the non-Oracle system to which the gateway connects.

The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You need one gateway instance, and therefore one gateway SID, for each ODBC source you are accessing.

If you want to access two ODBC sources, you need two gateway SIDs, one for each instance of the gateway. If you have only one ODBC source but want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, then you will need multiple gateway SIDs for the single ODBC source. The SID is used as part of the file name for the initialization parameter file.

Set the Initialization Parameter Values

After the initialization file has been created, you must set the initialization parameter values. A number of initialization parameters can be used to modify the gateway behavior. You must set the HS_FDS_CONNECT_INFO and the HS_FDS_SHAREABLE_NAME initialization parameters. Other initialization parameters have defaults or are optional. You can use the default values and omit the optional parameters, or you can specify the parameters with values tailored for your installation. Refer to Appendix C, "Initialization Parameters" for the complete list of initialization parameters that can be set. Changes made to the initialization parameters only take effect in the next gateway session.

The HS_FDS_CONNECT_INFO initialization parameter specifies the information required for connecting to the non-Oracle system. Set the HS_FDS_CONNECT_INFO as follows:

HS_FDS_CONNECT_INFO=dsn_value

where dsn_value is the data source name configured in the odbc.ini file

The HS_FDS_SHAREABLE_NAME initialization parameter specifies the full path of the ODBC driver manager. Set the HS_FDS_SHAREABLE_NAME as follows:

HS_FDS_SHAREABLE_NAME=full_path_of_odbc_driver

where full_path_of_odbc_driver is the full path to the ODBC driver manager

[ODBC Data Sources]
SQLServerWP=DataDirect 5.20 SQL Server Wire Protocol

[SQLServerWP]
Driver=/opt/odbc520/lib/ivmsss18.so
Description=DataDirect 5.20 SQL Server Wire Protocol
Database=oratst
LogonID=TKHOUSER
Password=TKHOUSER
Address=sqlserver-pc,1433
QuotedId=Yes
AnsiNPW=No

[ODBC]
Trace=0
TraceFile=/opt/odbc520/odbctrace.out
TraceDll=/opt/odbc520/lib/odbctrac.so
InstallDir=/opt/odb520
ConversionTableLocation=/opt/odbc520/tables
UseCursorLib=0

HS_FDS_CONNECT_INFO=SQLServerWP
HS_FDS_SHAREABLE_NAME=/opt/odbc520/lib/libodbc.so
set ODBCINI=/opt/odbc/odbc.ini

Configure Oracle Net Listener for the Gateway

The Oracle Net Listener listens for incoming requests from the Oracle database. For the Oracle Net Listener to listen for the gateway, information about the gateway must be added to the Oracle Net Listener configuration file, listener.ora. This file by default is located in $ORACLE_HOME/network/admin, where $ORACLE_HOME is the directory under which the gateway is installed.

The following entries must be added to the listener.ora file:

• A list of Oracle Net addresses on which the Oracle Net Listener listens

• The executable name of the gateway that the Oracle Net Listener starts in response to incoming connection requests

A sample of the listener.ora entry (listener.ora.sample) is available in the $ORACLE_HOME/hs/admin directory where $ORACLE_HOME is the directory under which the gateway is installed.

LISTENER=
        (ADDRESS= 
          (PROTOCOL=TCP)
          (HOST=host_name)
          (PORT=port_number))

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
      )
   )

SID_LIST_LISTENER=
(SID_LIST= 
   (SID_DESC=.
     .
   )
   (SID_DESC=.
     .
   )
   (SID_DESC=
      (SID_NAME=gateway_sid)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
   )
)

Stop and Start the Oracle Net Listener for the Gateway

You must stop and restart the Oracle Net Listener to initiate the new settings, as follows:

1. Set the PATH environment variable to $ORACLE_HOME/bin where $ORACLE_HOME is the directory in which the gateway is installed.

   For example on the Linux platform, if you have the Bourne or Korn Shell, enter the following:

   $ PATH=$ORACLE_HOME/bin:$PATH;export PATH
   $ LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
   

   If you have the C Shell, enter the following:

   $ setenv PATH $ORACLE_HOME/bin:$PATH
   $ setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$LD_LIBRARY_PATH
   

   Table 11-1 specifies which parameter value to use for the different platforms:

   Table 11-1 Parameter Values for UNIX Based Platforms

   Platform	Parameter Value
   Oracle Solaris (SPARC) 64 bit and Oracle Solaris on x86-64 (64-Bit)	LD_LIBRARY_PATH_64=$ORACLE_HOME/lib
   HP-UX Itanium	LD_LIBRARY_PATH=$ORACLE_HOME/lib
   Linux x86 64 bit	LD_LIBRARY_PATH=$ORACLE_HOME/lib
   IBM AIX on POWER Systems (64-Bit)	LIBPATH=$ORACLE_HOME/lib

   
   

2. If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:

   $ lsnrctl stop
   $ lsnrctl start
   

3. Check the status of the listener with the new settings, as follows:

   $ lsnrctl status
   

   The following is a partial output from a lsnrctl status check. In this example dg4odbc is the SID.

.
.
.
Services Summary...
Service "dg4odbc" has 1 instance(s).
  Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configuring tnsnames.ora

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is the syntax of the Oracle Net entry using the TCP/IP protocol:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
      (HS=OK))

Where:

Configuring tnsnames.ora for Multiple Listeners

To ensure higher availability, you can specify multiple listeners within the connect descriptor.

 connect_descriptor=
    (DESCRIPTION=
       (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=host_name_1)
          (PORT=port_number_1)
       )
       (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=host_name_2)
          (PORT=port_number_2)
       )
       (CONNECT_DATA=
          (SID=gateway_sid))
       (HS=OK))

This indicates that, if the listener for host_name_1 and port_number_1 is not available, then the second listener for host_name_2 and port_number_2 will take over.

Multiple ODBC Data Sources Example: Configuring the Gateway

Choose One System ID for Each ODBC Data Source

A separate instance of the gateway is needed for each ODBC data source. Each instance needs its own gateway System ID (SID). For this example, the gateway SIDs are chosen for the instances that access the ODBC data source:

• dg4odbc2 for the gateway accessing data source dsn2.

• dg4odbc3 for the gateway accessing data source dsn3.

Create Two Initialization Parameter Files

Create an initialization parameter file for each instance of the gateway by copying the original initialization parameter file $ORACLE_HOME/hs/admin/initdg4odbc.ora, twice, naming one with the gateway SID for dsn2 and the other with the gateway SID for dsn3:

$ cd ORACLE_HOME/hs/admin
$ cp initdg4odbc.ora initdg4odbc2.ora
$ cp initdg4odbc.ora initdg4odbc3.ora

Change the value of the HS_FDS_CONNECT_INFO parameter in the new files, as follows:

For initdg4odbc2.ora, enter the following:

HS_FDS_CONNECT_INFO=dsn2

For initdg4odbc3.ora, enter the following:

HS_FDS_CONNECT_INFO=dsn3

Multiple ODBC Data Sources Example: Configuring Oracle Net Listener

Add Entries to listener.ora

Add two new entries to the Oracle Net Listener configuration file, listener.ora. You must have an entry for each gateway instance, even when multiple gateway instances access the same database.

The following example shows the entry for the original installed gateway first, followed by the new entries.

SID_LIST_LISTENER=
(SID_LIST=
   (SID_DESC=
      (SID_NAME=dg4odbc)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
   )
   (SID_DESC=
      (SID_NAME=dg4odbc2)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
   )
   (SID_DESC=
      (SID_NAME=dg4odbc3)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
   )
)

where, oracle_home_directory is the directory where the gateway resides.

Multiple ODBC Data Sources Example: Stopping and Starting the Oracle Net Listener

If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:

$ lsnrctl stop
$ lsnrctl start

Multiple ODBC Data Sources Example: Configuring Oracle Database for Gateway Access

Add two connect descriptor entries to the tnsnames.ora file. You must have an entry for each gateway instance, even if the gateway instances access the same database.

The following example shows the entry for the original installed gateway first, followed by the two entries for the new gateway instances:

old_dsn_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4odbc))
               (HS=OK))
new_dsn2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4odbc2))
                (HS=OK))
new_dsn3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4odbc3))
                (HS=OK)) 

The value for PORT is the TCP/IP port number of the Oracle Net Listener that is listening for the gateway. The number can be found in the listener.ora file used by the Oracle Net Listener. The value for HOST is the name of the machine on which the gateway is running. The name also can be found in the listener.ora file used by the Oracle Net Listener.

Multiple ODBC Data Sources Example: Accessing ODBC Data

Enter the following to create a database link for the dg4odbc2 gateway:

SQL> CREATE PUBLIC DATABASE LINK ODBC2 CONNECT TO
  2  "user2" IDENTIFIED BY "password2" USING 'new_dsn2_using';

Enter the following to create a database link for the dg4odbc3 gateway:

SQL> CREATE PUBLIC DATABASE LINK ODBC3 CONNECT TO
  2  "user3" IDENTIFIED BY "password3" USING 'new_dsn3_using';

After the database links are created, you can verify the connection to the new ODBC data sources, as in the following:

SQL> SELECT * FROM ALL_USERS@ODBC2;

SQL> SELECT * FROM ALL_USERS@ODBC3;