4
Configuring the Gateway

After installing the gateway, perform the following tasks to configure the gateway for Sybase:

Configuring the Gateway

Perform the following tasks to configure the Oracle Transparent Gateway for Sybase.

Task 1: Choose a System ID 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

The entries for both the server_name and database_name are case-sensitive.

Additionally, set the Sybase environment variable, as follows:

Set SYBASE=sybase_dir

sybase_dir is the directory where the Sybase Adaptive Server is installed or the directory where the Sybase Open Client libraries are installed when the Sybase Adaptive Server is on a different machine from the gateway. This directory must be the same as that specified for the LIB environment variable.

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.

See Also:
Appendix C, "Heterogeneous Services Initialization Parameters" and the Oracle8i Distributed Database Systems for more information about customizing the initialization parameter file.

Configuring Net8 Listener for the Gateway

The gateway requires Net8 to provide transparent data access. After configuring the gateway, configure Net8 to work with the gateway.

Task 1: Configure Net8 TNS Listener for the Gateway

Net8 uses the TNS listener to receive incoming connections from a Net8 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 ORACLE_HOME directory under which the TNS listener is installed. This is the same ORACLE_HOME directory under which the gateway is installed.

Note:
If Net8 is reinstalled, the original listener.ora file is renamed and a new listener.ora file is put into the ORACLE_HOME\network\admin directory.

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

A list of Net8 addresses on which the TNS listener listens
The gateway that the TNS listener starts in response to incoming connection requests

Example of Address to Listen On in listener.ora File

The Oracle database server accesses the gateway using Net8 and the TCP/IP protocol adapter. The following is the syntax of the connect descriptor entry in the listener.ora file:

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

where:

host_name

is the name of the machine on which the gateway is installed.

port_number

specifies the port number used by the TNS listener. If you have other listeners running on host_name, the value of port_number must be different from the other listeners' port numbers.

Example of Gateway to Start in listener.ora File

To direct the TNS listener to start the gateway in response to incoming connection requests, add an entry to the listener.ora file with the following syntax:

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=tg4sybs)
      )
   )

where:

gateway_sid

specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the tnsnames.ora file.

oracle_home_directory

specifies the Oracle home directory where the gateway resides.

tg4sybs

specifies the Oracle Transparent Gateway for Sybase.

If you are already running a TNS listener that listens on multiple database SIDs, add only the following syntax to SID_LIST in the existing listener.ora file:

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

See Also:
Net8 Administrator's Guide for information about changing the listener.ora file.

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

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

From the Start menu, select Settings > Control Panel and select the Services icon.
Select the TNSListener service for the gateway.
If the service is already running, click Stop to stop it.
Click Start to start or restart the service.

Configuring the Oracle Database Server for Gateway Access

Any Oracle client connected to the Oracle database server can access Sybase data through the gateway. The Oracle client and the Oracle database server can reside on different machines. The gateway accepts connections only from the Oracle database server.

Before you use the gateway to access Sybase data you must configure the Oracle database server to enable communication with the gateway over Net8.

Configuring Oracle Net8 for the Oracle Database Server

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

See Also:
Oracle8i Administrator's Guide for information about editing the tnsnames.ora file.

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 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.

Configuring tnsnames.ora

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is an example of the Net8 entries using TCP/IP protocol needed for the Oracle database server to access the gateway:

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

where:

connect_descriptor

is the description of the object to connect to as specified when creating the database link, such as tg4sybs.
Check the sqlnet.ora file in the Oracle database server's ORACLE_HOME for the following lines:

names.directory_path = (TNSNAMES, HOSTNAME)

names.default_domain = world

name.default_zone = world

Note: If the Oracle database server is on Windows NT, the file is ORACLE_HOME\network\admin\sqlnet.ora.
If the sqlnet.ora file has these lines, connect_descriptor must end with the extension .world.

TCP

is the TCP protocol used for TCP/IP connections.

host_name

specifies the machine where the gateway is running.

port_number

matches the port number used by the Net8 TNS listener that is listening for the gateway. The TNS listener's port number can be found in the listener.ora file used by the TNS listener. See "Example of Address to Listen On in listener.ora File" on page 4-4.

gateway_sid

specifies the SID of the gateway and matches the SID specified in the listener.ora file of the TNS listener that is listening for the gateway. See "Task 1: Configure Net8 TNS Listener for the Gateway" on page 4-3 for more information.

(HS=OK)

specifies that this connect descriptor uses the Oracle Heterogeneous Services option.

Performing Optional Configuration Tasks

You can perform the following configuration tasks:

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.

See Also:
Oracle8i Distributed Database Systems for information about the two-phase commit process.

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.

Task 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 Sybase 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.

Note:
Oracle Corporation recommends that you use the default value RECOVER for the user name and password.

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

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

HS_FDS_RECOVERY_ACCOUNT to the user name of the Sybase user account you set up for recovery.

HS_FDS_RECOVERY_PWD to the password of the Sybase user account you set up for recovery.

See Also:
For information about editing the initialization parameter file, see "Task 2: Customize the Initialization Parameter File" on page 4-2. For information about HS_FDS_RECOVERY_ACCOUNT and HS_FDS_RECOVERY_PWD, see Appendix C, "Heterogeneous Services Initialization Parameters".

Task 2: Create the Transaction Log Table

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

Note:
The information in the transaction log table is required by the recovery process and must not be altered. The table must be used, accessed, or updated only by the gateway.

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

Create a transaction log table in the user account you created in "Task 1: 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 Sybase update takes place. Also, the transaction log table must be created under the owner of the recovery account.

Note:
To utilize the transaction log table, users of the gateway must be granted privileges on the table.

To create a transaction log table use the tg4sybs_tx.sql script, located in the directory ORACLE_HOME\tg4sybs\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 [-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.

See Also:
Appendix C, "Heterogeneous Services Initialization Parameters" for information about the HS_FDS_DEFAULT_OWNER initialization parameter.

Creating Database Links

A connection to the gateway is established through a database link when it is first used in an Oracle session. In this context, a connection refers to the connection between the Oracle database server and the gateway. The connection remains established until the Oracle session ends. Another session or user can access the same database link and get a distinct connection to the gateway and Sybase database.

Database links are active for the duration of a gateway session. If you want to close a database link during a session, you can do so with the ALTER SESSION statement. The database and application administrators of a distributed database system are responsible for managing the necessary database links that define paths to the Sybase database.

See Also:
Oracle8i Administrator's Guide and Oracle8i Distributed Database Systems for more information about using database links.

Configuring the Gateway for Multiple Sybase Databases

Note:
This section describes how to configure the gateway to access two Sybase databases created in the same Sybase server. The examples given here can be used to access different Sybase servers, provided that the SYBASE environment variable in each initialization parameter file is the same. If you must have different values for the SYBASE environment variable in each initialization parameter file, then you must start different listener processes. Refer to Net8 Administrator's Guide for more information about starting multiple listener processes for a single Oracle instance.

The tasks for configuring the gateway to access multiple Sybase databases are similar to the tasks for configuring the gateway for a single database. The configuration example assumes the following:

The gateway is installed and configured with the default SID of tg4sybs
The ORACLE_HOME environment variable is set to the directory where the gateway is installed
The gateway is configured for one Sybase database named db1
Two Sybase databases named db2 and db3 on a server named syb115_nt are being added

Configuring the gateway for additional Sybase databases is similar to configuring it for one database, and involves the following:

Configuring the gateway
Configuring the Sybase environment
Configuring Net8 for the gateway and the Oracle database server

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

> copy inittg4sybs.ora inittg4sybs2.ora

> copy inittg4sybs.ora inittg4sybs3.ora

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

For inittg4sybs2.ora, enter the following:

HS_FDS_CONNECT_INFO=syb115_nt.db2

For inittg4sybs3.ora, enter the following:

HS_FDS_CONNECT_INFO=syb115_nt.db3

Note:
If you have multiple gateway SIDs for the same Sybase database because you want to use different gateway parameter settings at different times, follow the same procedure. You create several initialization parameter files, each with different SIDs and different parameter settings.

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_dir

For inittg4sybs3.ora, enter the following:

SET SYBASE=sybase_dir

sybase_dir is the directory that was specified for the LIB environment variable.

Multiple Databases Example: Configuring Oracle Net8 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)
   )
   (SID_DESC=
      (SID_NAME=tg4sybs2)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
   )
   (SID_DESC=
      (SID_NAME=tg4sybs3)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=tg4sybs)
   )
)

Multiple Databases Example: Stopping and Starting the TNS Listener

From the Start menu, select Settings > Control Panel and select the Services icon. A list of all NT services displays.
Select the TNSListener service for the gateway.
Click Stop.
Click Start.

Multiple Databases Example: Configuring the Oracle Database Server for Gateway Access

Configuring Net8 on the Oracle Database Server for Multiple Gateway Instances

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_link=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4sybs))
               (HS=OK))
new_db2_link=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4sybs2))
                (HS=OK))
new_db3_link=(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 'tg4sybs2';

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 'tg4sybs3';

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

SQL> SELECT * FROM ALL_USERS@SYBS2;

SQL> SELECT * FROM ALL_USERS@SYBS3;

host_name	is the name of the machine on which the gateway is installed.
port_number	specifies the port number used by the TNS listener. If you have other listeners running on host_name, the value of port_number must be different from the other listeners' port numbers.

gateway_sid	specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the tnsnames.ora file.
oracle_home_directory	specifies the Oracle home directory where the gateway resides.
tg4sybs	specifies the Oracle Transparent Gateway for Sybase.

connect_descriptor	is the description of the object to connect to as specified when creating the database link, such as tg4sybs. Check the sqlnet.ora file in the Oracle database server's ORACLE_HOME for the following lines: `names.directory_path = (TNSNAMES, HOSTNAME)` `names.default_domain = world` `name.default_zone = world` Note: If the Oracle database server is on Windows NT, the file is `ORACLE_HOME\network\admin\sqlnet.ora`. If the `sqlnet.ora` file has these lines, connect_descriptor must end with the extension `.world`.
`TCP`	is the TCP protocol used for TCP/IP connections.
host_name	specifies the machine where the gateway is running.
port_number	matches the port number used by the Net8 TNS listener that is listening for the gateway. The TNS listener's port number can be found in the `listener.ora` file used by the TNS listener. See "Example of Address to Listen On in listener.ora File" on page 4-4.
gateway_sid	specifies the SID of the gateway and matches the SID specified in the `listener.ora` file of the TNS listener that is listening for the gateway. See "Task 1: Configure Net8 TNS Listener for the Gateway" on page 4-3 for more information.
`(HS=OK)`	specifies that this connect descriptor uses the Oracle Heterogeneous Services option.

4Configuring the Gateway

Configuring the Gateway

Task 1: Choose a System ID for the Gateway

Task 2: Customize the Initialization Parameter File

Configuring Net8 Listener for the Gateway

Task 1: Configure Net8 TNS Listener for the Gateway

Example of Address to Listen On in listener.ora File

Example of Gateway to Start in listener.ora File

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

Configuring the Oracle Database Server for Gateway Access

Configuring Oracle Net8 for the Oracle Database Server

Configuring tnsnames.ora

Performing Optional Configuration Tasks

Configuring for Two-Phase Commit

Task 1: Create a Recovery Account and Password

Task 2: Create the Transaction Log Table

Specifying an Owner

Creating Database Links

Configuring the Gateway for Multiple Sybase Databases

Multiple Databases Example: Configuring the Gateway

Multiple Databases Example: Configuring the Sybase Environment

Multiple Databases Example: Configuring Oracle Net8 Listener

Add Entries to listener.ora

Multiple Databases Example: Stopping and Starting the TNS Listener

Multiple Databases Example: Configuring the Oracle Database Server for Gateway Access

Configuring Net8 on the Oracle Database Server for Multiple Gateway Instances

Multiple Databases Example: Accessing Sybase Data

4
Configuring the Gateway