2 Configuring the Gateway

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

You can define a gateway SID, but using the default of tg4sybs is easier because you do not need to change the initialization parameter file name. However, if you want to access two Sybase databases, you need two gateway SIDs, one for each instance of the gateway. If you have one Sybase database and want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, you can do that by having multiple gateway SIDs for the single Sybase database.

Task 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/tg4sybs/admin/inittg4sybs.ora

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

If you are not using tg4sybs as the gateway SID, you must rename the initialization parameter file using the SID you chose in Task 1. This default initialization parameter file is sufficient for starting the gateway, verifying a successful installation, and running the demonstration scripts.

In the initialization parameter file, specify the Sybase connection as follows:

HS_FDS_CONNECT_INFO=server_name.database_name[,INTERFACE=interface_file]

The entries for both the server_name and database_name are case-sensitive. The entry for the interface_file includes the full path and name of the file.

Additionally, set the Sybase environment variable, as follows:

Set SYBASE=sybase

Where sybase is the directory referred to in the $SYBASE environment variable (the home of the Sybase software installation).

A number of initialization parameters can be used to modify gateway behavior. You might want to change the initialization parameter file later to meet system requirements.

Task 1: Configure Oracle Net Services TNS Listener for the Gateway

Oracle Net Services uses the TNS listener to receive incoming connections from a Oracle Net Services client. The TNS listener and the gateway must reside on the same machine.

The TNS listener listens for incoming requests from the Oracle database server. For the TNS listener to listen for the gateway, information about the gateway must be added to the TNS listener configuration file, listener.ora. This file 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 Services addresses on which the TNS listener listens

• The gateway that the TNS listener starts in response to incoming connection requests

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=tg4sybs)
          (ENVS=LD_LIBRARY_PATH=sybase/sybase_ocs/lib:                                              oracle_home_directory/lib32)
      )
   )

SID_LIST_LISTENER=
(SID_LIST= 
   (SID_DESC=.
     .
   )
   (SID_DESC=.
     .
   )
   (SID_DESC=
      (SID_NAME=gateway_sid)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
      (ENVS=LD_LIBRARY_PATH=sybase/sybase_ocs/lib:oracle_home_directory/lib32)
   )
)

Task 2: Stop and Start the TNS Listener for the Gateway

The TNS listener must be started to initiate the new settings, as follows:

1. Set the PATH environment variable to access the commands in the directory $ORACLE_HOME/bin where the gateway is installed. 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
   
   

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 an example of output from a lsnrctl status check:

LSNRCTL for Solaris: Version 10.2.0.1 - Production on 01-JUN-2005 09:28:13Copyright (c) 1991, 2004, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551))STATUS of the LISTENER----------------------Alias                    listenerVersion                  TNSLSNR for Solaris: Version 10.2.0.1 - ProductionStart Date               28-APRIL-2005 15:38:59Uptime                   33 days 17 hr. 49 min. 22 secTrace Level              offSecurity                 OFFSNMP                     OFFListener Parameter File  /users/oracle/gateway/network/admin/listener.oraListener Log File        /users/oracle/gateway/network/log/listener.logListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551)))Services Summary...Service "tg4sybs" has 1 instance(s).  Instance "tg4sybs", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully

In this example, tg4sybs is the default SID value assigned during installation. You can use any valid ID for the SID, or keep the default.

Configuring Oracle Net Services for the Oracle Database Server

To configure the server you add connect descriptors to the tnsnames.ora file. You cannot use the Oracle Net Services Assistant or the Oracle Net Services Easy Config tools to configure the tnsnames.ora file. You must edit the file manually.

For the Oracle database server to access the gateway, it needs a service name entry or a connect descriptor name entry in the tnsnames.ora file to tell the Oracle database server where to make connections.

By default, this file is located in $ORACLE_HOME/network/admin, where $ORACLE_HOME is the directory in which the Oracle database server is installed. The tnsnames.ora file is required by the Oracle database server accessing the gateway, but not by the gateway.

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

Multiple Databases Example: Configuring the Gateway

Choose Two System IDs for Each Sybase Database

A separate instance of the gateway accesses the different Sybase databases. Each instance needs its own gateway System ID (SID). For this example, the gateway SIDs are chosen for the instances that access the Sybase databases:

• tg4sybs2 for the gateway accessing database db2

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

$ cd $ORACLE_HOME/tg4sybs/admin$ cp inittg4sybs.ora inittg4sybs2.ora$ cp inittg4sybs.ora inittg4sybs3.ora

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

For inittg4sybs2.ora, enter the following:

HS_FDS_CONNECT_INFO=syb120_sun.db2

For inittg4sybs3.ora, enter the following:

HS_FDS_CONNECT_INFO=syb120_sun.db3

Multiple Databases Example: Configuring the Sybase Environment

Set the Sybase environment variables in both of the new initialization parameter files, as follows:

For inittg4sybs2.ora, enter the following:

Set SYBASE=sybase

For inittg4sybs3.ora, enter the following:

Set SYBASE=sybase

Where sybase is the directory referred to in the $SYBASE environment variable (the home of the Sybase software installation).

Multiple Databases Example: Configuring Oracle Net Services Listener

Add Entries to listener.ora

Add two new entries to the TNS 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=tg4sybs)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
      (ENVS=LD_LIBRARY_PATH=sybase/sybase_ocs/lib:oracle_home_directory/lib32)
   )
   (SID_DESC=
      (SID_NAME=tg4sybs2)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
      (ENVS=LD_LIBRARY_PATH=sybase/sybase_ocs/lib:oracle_home_directory/lib32)
   )
   (SID_DESC=
      (SID_NAME=tg4sybs3)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
      (ENVS=LD_LIBRARY_PATH=sybase/sybase_ocs/lib:oracle_home_directory/lib32)
   )
)

Multiple Databases Example: Stopping and Starting the TNS 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 Databases Example: Configuring the Oracle Database Server 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 Sybase example shows the entry for the original installed gateway first, followed by the two entries for the new gateway instances:

old_db_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4sybs))
               (HS=OK))
new_db2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4sybs2))
                (HS=OK))
new_db3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4sybs3))
                (HS=OK)) 

The value for PORT is the TCP/IP port number of the TNS listener that is listening for the gateway. The number can be found in the listener.ora file used by the TNS 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 TNS listener.

Multiple Databases Example: Accessing Sybase Data

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

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

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

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

After the database links are established you can query the new Sybase databases, as in the following example:

SQL> SELECT * FROM ALL_USERS@SYBS2;

Or

SQL> SELECT * FROM ALL_USERS@SYBS3;

Configuring for Two-Phase Commit

The gateway supports the following transaction capabilities:

• COMMIT_CONFIRM

• READ_ONLY

• SINGLE_SITE

By default, the gateway runs in COMMIT_CONFIRM transaction mode. When the Sybase database is updated by a transaction, the gateway becomes the commit point site. The Oracle database server commits the unit of work in the Sybase database after verifying that all Oracle databases in the transaction have successfully prepared the transaction. Only one gateway can participate in an Oracle two-phase commit transaction as the commit point site.

To enable the COMMIT_CONFIRM transaction mode, create a recovery account and password and create a log table. The log table, called HS_TRANSACTION_LOG, is where two-phase commit transactions are recorded.

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

Specifying an Owner

Instead of using the default owner name for the Sybase tables as defined in Sybase, or explicitly specifying a different owner in the SQL statements, you can specify a default owner that is used whenever a name is not explicitly specified in the SQL statements.

To specify the owner, set the gateway initialization parameter HS_FDS_DEFAULT_OWNER in the initialization parameter file.