2 Configuring the Gateway

2.1.1 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 different set of gateway metadata (ADD) definitions, where each set is stored in a different directory. The SID is used as part of the file name for the initialization parameter file. The default SID is tg4rms.

You can define a gateway SID, but using the default of tg4rms is easier because you do not need to change the initialization parameter file name. However, if you want to access RMS data using different sets of gateway ADD metadata, you need two gateway SIDs, one for each instance of the gateway. If you have one set of gateway ADD metadata 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.

2.1.2 Task 2: Customize the Gateway Startup File

The gateway startup script initializes certain logical names and starts the gateway. The script is started by the Oracle Net Services listener after an incoming connect request for the gateway is received. The following default startup script, oracle_home:[tg4rms.admin]tg4rms.com, is created as part of the installation (oracle_home is the device under which the gateway is installed):

$! TG4RMS.COM - created by ORA_TG4RMS_BLD.COM
$ sid = "TG4RMS"
$!
$!
$!------------DO NOT EDIT BELOW THIS LINE--------------
$!
$ @oracle_home:[util]orauser
$!
$ define/job/nolog oracle_sid 'sid'
$ define/job/nolog ora_sid 'sid'
$!
$ TG4RMS "(LOCAL=NO)"
$! 

To customize the startup script, modify the line which sets the value for sid.

2.1.3 Task 3: 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:[tg4rms.admin]inittg4rms.ora

where oracle_home is the device under which the gateway is installed.

If you are not using tg4rms 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 RMS connection as follows:

HS_FDS_CONNECT_INFO=data_dictionary_directory

where data_dictionary_directory is the directory where the data dictionary files are located. The default value in the initialization parameter file is set as follows:

ora_tg4rms_dd

which is an OpenVMS logical that points to oracle_home:[tg4rms.data_dictionary].

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.

2.2.1 Installing the PL/SQL Package DBMS_TG4RMS

The Oracle Transparent Gateway for RMS comes with a PL/SQL package called DBMS_TG4RMS. This is an administrative package that consists of the following procedures:

This package is not pre-installed in any of the Oracle databases. A DBA must create these packages in every Oracle database from which these procedures will be called. The package is represented by the following three files: catrms.sql, dbmsrms.sql, and prvtrms.plb. These files can be found in oracle_home:[tg4rms.admin].

To create this package:

1. Copy the three files to the ORACLE_HOME of the database in which you want to create this package (the scripts are platform independent).

2. Connect to the Oracle database as SYS using SQLPlus.

3. Issue the following statement from SQLPlus:

   SQL> @catrms 
   

2.2.2 Working With the Gateway Data Dictionary

The gateway data dictionary can be populated in one of the following ways:

• Creating ADD Definitions Using the ADD Language

• Extending ADD Definitions Using the ADD Language

DEFINE RECORD NATION
   FILENAME IS "DKA300:[USER.RMS_DEMO]nation.INX"
   ORGANIZATION IS INDEX
   N_ROWS IS 25
   RECORD_FORMAT IS FIXED.
   FIELDS.
      N_NATIONKEY
         DATATYPE IS LONGWORD.
      N_NAME
         DATATYPE IS STRING
         SIZE IS 25 CHARACTERS.
      N_REGIONKEY
         DATATYPE IS LONGWORD.
      N_COMMENT
         DATATYPE IS STRING
         SIZE IS 152 CHARACTERS.
   END FIELDS.
   INDEXES.
      NINDEX.
         N_NATIONKEY.
      END NINDEX.
   END INDEXES.
END RECORD.

begin 
   dbms_tg4rms.addl_to_add('addl_file', 'dblink');
end; 
/

begin 
   dbms_tg4rms.add_to_addl('rms_record', 'addl_file', 'dblink'); 
end; 
/

2.3.1 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 device 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)
     (PROGRAM=oracle_home:[tg4rms.admin]tg4rms.com)
    )
 )

SID_LIST_LISTENER=
(SID_LIST= 
   (SID_DESC=.
     .
   )
   (SID_DESC=.
     .
   )
   (SID_DESC=
     (SID_NAME=gateway_sid)
     (PROGRAM=oracle_home:[tg4rms.admin]tg4rms.com)
   )
)

2.3.2 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 HP OpenVMS logicals and symbols required to start and stop the gateway listener by executing the orauser.com script located in the utility directory, as follows:

   $ @oracle_home:[util]orauser.com
   
   

   Where oracle_home is the device where the gateway is installed.

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 VMS: Version 10.1.0.2.0 - Production on 07-DEC-2004 12:26:42

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551))
STATUS of the LISTENER
----------------------
Alias                    LISTENER
Version                  TNSLSNR for VMS: Version 10.1.0.2.0 - Production
Start Date               07-DEC-2004 10:16:17
Uptime                   0 days 2 hr. 10 min. 25 sec
Trace Level              off
Security                 OFF
SNMP                     OFF
Listener Parameter File  ALPHA$DKA500:[ORA901.NETWORK.ADMIN]listener.ora

Listener Log File        DISK5:[ORA901.network.log]listener.log

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551)))
Services Summary...
Service "tg4rms" has 1 instance(s).
  Instance "tg4rms", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

2.4.1 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 in oracle_home: [network.admin], where oracle_home is the device under which the gateway 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))

2.6.1 Multiple Data Dictionaries Example: Configuring the Gateway

Choose Two System IDs for Each RMS Data Dictionary

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

• tg4rms2 for the gateway accessing data dictionary db2

• tg4rms3 for the gateway accessing data dictionary db3

Create Two Gateway Startup Files

Create a startup file for each gateway sid; tg4rms2.com for gateway sid tg4rms2 and tg4rms3.com for gateway sid tg4rms3, by copying the default startup file that was created during install (tg4rms).

Change the value of the SID in these files tg4rms2 and tg4rms3 respectively.

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:[tg4rms.admin]inittg4rms.ora, twice, naming one with the gateway SID for db2 and the other with the gateway SID for db3:

$ cd oracle_home:[tg4rms.admin]
$ copy inittg4rms.ora inittg4rms2.ora
$ copy inittg4rms.ora inittg4rms2.ora

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

For inittg4rms2.ora, enter the following:

HS_FDS_CONNECT_INFO=data_dictionary_directory2

For inittg4rms3.ora, enter the following:

HS_FDS_CONNECT_INFO=data_dictionary_directory3

where data_dictionary_directory2 and data_dictionary_directory3 are the different directories where the gateway metadata is stored.

2.6.2 Multiple Data Dictionaries 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 data dictionary.

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=tg4rms)
     (PROGRAM=oracle_home:[tg4rms.admin]tg4rms.com)
   )
   (SID_DESC=
     (SID_NAME=tg4rms2)
     (PROGRAM=oracle_home:[tg4rms.admin]tg4rms2.com)
   )
   (SID_DESC=
     (SID_NAME=tg4rms3)
     (PROGRAM=oracle_home:[tg4rms.admin]tg4rms3.com)
   )
)

2.6.3 Multiple Data Dictionaries Example: Stopping and Starting the TNS Listener

Set the HP OpenVMS logicals required to start the gateway listener by executing the orauser.com script located in the utility directory, as follows:

$ @oracle_home:[util]orauser.com

where oracle_home is the device under which the gateway is installed.

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

2.6.4 Multiple Data Dictionaries Example: Configuring the Oracle Database Server for Gateway Access

Configure Oracle Net Services 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 data dictionary.

The following RMS 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=tg4rms))
               (HS=OK))
new_db2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4rms2))
                (HS=OK))
new_db3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=tg4rms3))
                (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.

2.6.5 Multiple Data Dictionaries Example: Accessing RMS Data

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

SQL> CREATE PUBLIC DATABASE LINK RMS2 USING 'new_db2_using';

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

SQL> CREATE PUBLIC DATABASE LINK RMS3 USING 'new_db3_using';

After the database links are established you can query the RMS data, as in the following:

SQL> SELECT * FROM ALL_USERS@RMS2;

or

SQL> SELECT * FROM ALL_USERS@RMS3;