14 Configuring Oracle Database Gateway for DRDA

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 DRDA database you are accessing. However, if you want to access two DRDA databases, you need two gateway SIDs, one for each instance of the gateway. If you have one DRDA 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 DRDA database. The SID is used as part of the file name for the initialization parameter file.

Customize the Initialization Parameter File

The data in this chapter describes the configuration process for the gateway. You should notice that most, if not all, gateway parameters have been moved into the initsid.ora initialization file. To configure the host for the Oracle Database Gateway for IBM DRDA, you must tailor the parameter files for your installation.

1. Copy the gateway initialization

   Sample gateway initialization files (initsid.ora) are shipped on the distribution media. These files are in the ORACLE_HOME\dg4drda\admin directory:

   • initDB2.ora, for DB2/OS390 remote servers

   • initAS400.ora, for DB2/400 remote servers

   • initDB2UDB.ora, for DB2/UDB remote servers

   Copy one of these sample files into the same directory, renaming it with the name of your gateway SID. For example, if you chose your SID to be DRD1, and if your remote server is DB2, then copy the initDB2.ora file and rename it initDRD1.ora.

2. Determine settings for gateway initialization parameters

   Your Configuration Worksheet in Appendix D, "Configuration Worksheet for DRDA" should be complete. If not, review the incomplete entries and refer to the sections listed for more information. You need this information to tailor the gateway initialization file, initsid.ora.

   Refer to Appendix C, "Initialization Parameters" for information on the DRDA-specific initsid.ora parameters.

   Following are the parameters:

   • Required Parameters

     When you edit your initsid.ora file, you must change the values of all the parameters listed in the Configuration Worksheet in Appendix D, "Configuration Worksheet for DRDA", using the values in the right-hand column of the worksheet.

     You will also need to set certain Globalization Support gateway parameters. For more information on setting these parameters, refer to Appendix E, "Globalization Support for DRDA".

   • Optional Parameters

     Several DRDA-specific parameters are not required, but you might want to change them. Unless otherwise indicated, these parameters are described in Appendix C, "Initialization Parameters".

     Table 14-1 Optional DRDA-Specific Parameters

     DRDA parameters	Description
     DRDA_DISABLE_CALL	Used to disable stored procedure support for DRDA Servers on which the gateway does not support stored procedures
     DRDA_ISOLATION_LEVEL	Defines the package Isolation Level
     HS_DB_NAME	Specifies the database SID name and must be set to the gateway SID
     DRDA_OPTIMIZE_QUERY	Used for data query optimization
     DRDA_PACKAGE_COLLID	Defines the package collection ID
     DRDA_PACKAGE_NAME	Defines the name of the package
     DRDA_PACKAGE_OWNER	Defines the owner of the package. By default, the owner is the user ID that is used when you run the g4drutl bind utility. This parameter is not valid for SQL/DS.
     DRDA_PACKAGE_SECTIONS	Defines the maximum number of concurrent OPEN cursors at the remote server
     HS_DB_DOMAIN	Specifies the gateway database domain

     
     

     The values that are set in your initsid.ora file should work for most installations. Edit the values if changes are needed. For information on Globalization Support-related initsid.ora parameters, refer to "Globalization Support Parameters in the Gateway Initialization File".

3. Tailor the initsid.ora File

   After you have copied the sample initialization file, you will need to tailor it to your installation. While many parameters can be left to their defaults, some parameters must be changed for correct operation of the gateway. Give attention to the following DRDA and HS parameters. Also, give attention to the security aspects of the initialization file. Chapter 15, "Security Considerations", contains details on encryption of passwords that would otherwise be embedded in the initialization file. See Appendix D, "Configuration Worksheet for DRDA" for a description of each parameter:

   • DRDA_CONNECT_PARM

   • DRDA_PACKAGE_COLLID

   • DRDA_PACKAGE_NAME

   • DRDA_PACKAGE_OWNER

   • DRDA_REMOTE_DB_NAME

   • FDS_CLASS

   • HS_DB_NAME

   • HS_DB_DOMAIN

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\dg4drda\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=g4drsrv)
      )
   )

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

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. From the Start menu, select Settings, Control Panel and then select Services.

2. Select the Oracle Net Listener service for the gateway.

3. If the service is already running, click Stop to stop it.

4. Click Start to start or restart the service.

DRDA Gateway Package Binding Considerations

Before binding the DRDA gateway package, perform the following steps:

1. Check the user has the appropriate privileges

   The DRDA package must be bound with the internal stored procedure GTW$_BIND_PKG. The user ID used to bind the DRDA package must have the appropriate privileges on the remote database, as described in Chapter 13, "Configuring the DRDA Server".

2. Check all DRDA parameter settings

   Check all DRDA parameter settings to be sure that they are set correctly before you start the bind. For example, the default for DRDA_DISABLE_CALL only works if your DRDA database supports stored procedures. If not, then you must change the setting. Also, the value for DRDA_PACKAGE_NAME must be unique if you have any older versions of the gateway installed. New packages replace any old packages with the same name, causing versions of the gateway that use the old package to fail. Refer to Appendix C, "Initialization Parameters" for information on the parameters and their settings.

DRDA Gateway Package Binding Steps

1. Log on to an Oracle database

   Use SQL*Plus:

   > sqlplus system/manager
   

2. Create a database link using the user ID that you chose while configuring the DRDA Server.

   SQL> CREATE PUBLIC DATABASE LINK dblink
   2 CONNECT TO "userid" IDENTIFIED BY "password" 
   3 USING 'tns_name_entry' 
   

   Note:

   The user ID that is creating the public database link must have the CREATE PUBLIC DATABASE LINK privilege.

3. Run the stored procedure GTW$_BIND_PKG

   SQL> exec GTW$_BIND_PKG@dblink; 
   SQL> COMMIT;
   

   This creates and commits the package. If any errors are reported, then correct the gateway initialization file parameters as needed.

Configuring tnsnames.ora

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is a 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:

Multiple DRDA Databases Example: Configuring the Gateway

Choose One System ID for Each DRDA Database

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

• dg4drda2 for the gateway accessing database db2.

• dg4drda3 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\dg4drda\admin\initdg4drda.ora, twice, naming one with the gateway SID for db2 and the other with the gateway SID for db3:

> cd ORACLE_HOME\dg4drda\admin> copy initdg4drda.ora initdg4drda2.ora> copy initdg4drda.ora initdg4drda3.ora

Multiple DRDA 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=dg4drda)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=g4drsrv)
   )
   (SID_DESC=
      (SID_NAME=dg4drda2)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=g4drsrv)
   )
   (SID_DESC=
      (SID_NAME=dg4drda3)
      (ORACLE_HOME=oracle_home_directory)
      (PROGRAM=g4drsrv)
   )
)

where, oracle_home_directory is the directory where the gateway resides.

Multiple DRDA Databases Example: Stopping and Starting the Oracle Net Listener

Perform the following steps:

1. From the Start menu, select Settings, Control Panel and then select Services.

2. Select the Oracle Net Listener service for the gateway.

3. Click Stop.

4. Click Start.

Multiple DRDA Databases 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.

This example describes how to configure Oracle Net on the Oracle database for multiple gateway instances. It 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=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4drda))
               (HS=OK))
new_db2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4drda2))
                (HS=OK))
new_db3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=port_number)
                (HOST=host_name))
                (CONNECT_DATA=
                    (SID=dg4drda3))
                (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 DRDA Databases Example: Accessing DB2 Data

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

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

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

SQL> CREATE PUBLIC DATABASE LINK DRDA3 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 DRDA databases, as in the following:

SQL> SELECT * FROM ALL_USERS@DRDA2;

SQL> SELECT * FROM ALL_USERS@DRDA3;