9 Configuring Oracle Database Gateway for SQL Server

9.1.1 Choose a System Identifier for the Gateway

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 SQL Server database you are accessing. The SID is used as part of the file name for the initialization parameter file. The default SID is dg4msql.

You can define a gateway SID, but using the default of dg4msql is easier because you do not need to change the initialization parameter file name. However, if you want to access two SQL Server databases, you need two gateway SIDs, one for each instance of the gateway. If you have only one SQL Server database and 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 SQL Server database.

9.1.2 Customize the Initialization Parameter File

The initialization parameter file must be available when the gateway is started. During installation, the following default initialization parameter file is created:

$ORACLE_HOME/dg4msql/admin/initdg4msql.ora

Where $ORACLE_HOME is the directory under which the gateway is installed.

This initialization file is for the default gateway SID. If you are not using dg4msql as the gateway SID, you must rename the initialization parameter file using the SID you chose in the preceding step "Choose a System Identifier for the Gateway". This default initialization parameter file is sufficient for starting the gateway, verifying a successful installation, and running the demonstration scripts.

A number of initialization parameters can be used to modify the gateway behavior. 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 most important parameter is the HS_FDS_CONNECT_INFO, which describes the connection to the non-Oracle system.

The default initialization parameter file already has an entry for this parameter. The syntax for HS_FDS_CONNECT_INFO is as follows:

HS_FDS_CONNECT_INFO=host_name[[:port_number]|/[instance_name]][/database_name]

Where:

Either of the variables port_number or instance_name can be used, but not both together. Optionally, they both can be omitted. The variable database_name is always optional. The slash (/) is required when a particular value is omitted. For example, all of the following entries are valid:

HS_FDS_CONNECT_INFO=host_name/instance_name/database_name
HS_FDS_CONNECT_INFO=host_name//database_name
HS_FDS_CONNECT_INFO=host_name:port_name//database_name
HS_FDS_CONNECT_INFO=host_name/instance_name
HS_FDS_CONNECT_INFO=host_name 

This release of gateway can support IPv6. If IPv6 address format is to be specified, you have to wrap it with square brackets to indicate the separation from the port number. For example,

HS_FDS_CONNECT_INFO=[2001:0db8:20C:F1FF:FEC6:38AF]:1300//SQL_DB1

9.2.1 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/dg4msql/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=dg4msql)
      )
   )

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

SID_LIST_LISTENER=
(SID_LIST= 
   (SID_DESC=.
     .
   )
   (SID_DESC=.
     .
   )
   (SID_DESC=
      (SID_NAME=gateway_sid)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4msql)
   )
)

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

9.2.2 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 9-1 specifies which parameter value to use for the different platforms:

   Table 9-1 Parameter Values for UNIX Based Platforms

   Platform	Parameter Value
   Solaris (SPARC) 64 bit	LD_LIBRARY_PATH_64=$ORACLE_HOME/lib
   HP-UX Itanium	LD_LIBRARY_PATH=$ORACLE_HOME/lib
   Linux x86, and Linux x86 64 bit	LD_LIBRARY_PATH=$ORACLE_HOME/lib
   AIX	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:

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

In this example, the service name is dg4msql, which is the default SID value assigned during installation.

9.3.1 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:

9.5.1 Create a Recovery Account and Password

For the gateway to recover distributed transactions, a recovery account and password must be set up in the SQL Server database. By default, both the user name of the account and the password are RECOVER. The name of the account can be changed with the gateway initialization parameter HS_FDS_RECOVERY_ACCOUNT. The account password can be changed with the gateway initialization parameter HS_FDS_RECOVERY_PWD.

1. Set up a user account in the SQL Server database. Both the user name and password must be a valid SQL Server user name and password.

2. In the initialization parameter file, set the following gateway initialization parameters:

   • HS_FDS_RECOVERY_ACCOUNT to the user name of the SQL Server user account you set up for recovery.

   • HS_FDS_RECOVERY_PWD to the password of the SQL Server user account you set up for recovery.

     See Also:

     "Customize the Initialization Parameter File" for information about editing the initialization parameter file. For information about HS_FDS_RECOVERY_ACCOUNT and HS_FDS_RECOVERY_PWD, see Appendix C, "Initialization Parameters".

9.5.2 Create the Transaction Log Table

When configuring the gateway for two-phase commit, a table must be created in the SQL Server database for logging transactions. The gateway uses the transaction log table to check the status of failed transactions that were started at the SQL Server database by the gateway and registered in the table.

The table, called HS_TRANSACTION_LOG, consists of two columns, GLOBAL_TRAN_ID, data type CHAR(64) NOT NULL and TRAN_COMMENT, data type CHAR(255).

You can use another name for the log table, other than HS_TRANSACTION_LOG, by specifying the other name using the HS_FDS_TRANSACTION_LOG initialization parameter.

Create the transaction log table in the user account you created in "Create a Recovery Account and Password". Because the transaction log table is used to record the status of a gateway transaction, the table must reside at the database where the SQL Server update takes place. Also, the transaction log table must be created under the owner of the recovery account.

To create a transaction log table use the dg4msql_tx.sql script, located in the directory $ORACLE_HOME/dg4msql/admin where $ORACLE_HOME is the directory under which the gateway is installed. Use isql to execute the script, as follows:

$ isql -Urecovery_account -Precovery_account_password [-Sserver] -idg4msql_tx.sql

9.8.1 Multiple SQL Server Databases Example: Configuring the Gateway

Choose One System ID for Each SQL Server Database

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

• dg4msql2 for the gateway accessing database db2

• dg4msql3 for the gateway accessing database db3

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/dg4msql/admin/initdg4msql.ora, twice, naming one with the gateway SID for db2 and the other with the gateway SID for db3:

$ cd $ORACLE_HOME/dg4msql/admin
$ cp initdg4msql.ora initdg4msql2.ora
$ cp initdg4msql.ora initdg4msql3.ora

Change the value of the HS_FDS_CONNECT_INFO parameter in the new files.

For initdg4msql2.ora, enter the following:

HS_FDS_CONNECT_INFO=204.179.79.15:1433//db2

For initdg4msql3.ora, enter the following:

HS_FDS_CONNECT_INFO=204.179.79.15:1433//db3

9.8.2 Multiple SQL Server Databases 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=dg4msql)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4msql)
   )
   (SID_DESC=
      (SID_NAME=dg4msql2)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4msql)
   )
   (SID_DESC=
      (SID_NAME=dg4msql3)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=dg4msql)
   )
)

where, oracle_home_directory is the directory where the gateway resides.

9.8.3 Multiple SQL Server Databases 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

9.8.4 Multiple SQL Server Databases Example: Configuring Oracle Database for Gateway Access

old_db_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4msql))
               (HS=OK))
new_db2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4msql2))
                (HS=OK))
new_db3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4msql3))
                (HS=OK)) 

9.8.5 Multiple SQL Server Databases Example: Accessing SQL Server Data

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

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

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

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

After the database links are created, you can verify the connection to the new SQL Server databases, as in the following:

SQL> SELECT * FROM ALL_USERS@MSQL2;

SQL> SELECT * FROM ALL_USERS@MSQL3;