Skip Headers
Oracle® Database Gateway Installation and Configuration Guide
11g Release 1 (11.1) for Microsoft Windows

B31043-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

14 Configuring Oracle Database Gateway for DRDA

After installing the gateway, perform the following tasks to configure Oracle Database Gateway for DRDA:

  1. Configure the Gateway Initialization Parameter File

  2. Configure Oracle Net for the Gateway

  3. Configure Two-Phase Commit

  4. Bind the DRDA Gateway Package

  5. Create Tables and Views for Data Dictionary Support

  6. Grant Authority to the DRDA Package

  7. Configure the Oracle Database for Gateway Access

  8. Create Database Links

  9. Configure the Gateway to Access Multiple DRDA Databases

SQL scripts are provided to perform steps such as creating the ORACLE2PC table, removing obsolete tables and views, and creating tables and views to provide data dictionary support.

These scripts must be run on the DRDA Server platform using a database native tool (such as SPUFI on DB2/OS390), because no tool is provided with the gateway to execute these scripts. Note that when running these scripts, the user ID used must be suitably authorized.

Choose the appropriate subdirectory for your DRDA server platform from the following list:

Configure the Gateway Initialization Parameter File

Perform the following tasks to configure the gateway initialization parameter file

  1. Choose a System Identifier for the Gateway

  2. Customize the Initialization Parameter File

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.

Note:

In previous versions of the gateway, the initialization parameters were stored in the files named initsid.ora and initsid.gtwboot in the gateway instance directories. With Release 10.1.0.2.0 of the gateway, most parameters that were in'initsid.gtwboot' have been moved to initsid.ora. The syntax of the initsid.ora has been simplified. Refer to Appendix C, "Initialization Parameters" for details.

When migrating from previous releases of DG4DRDA, be aware of these differences.

  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 for the Gateway

The gateway requires Oracle Net to communicate with the Oracle database. After configuring the gateway, perform the following tasks to configure Oracle Net to work with the gateway:

  1. Configure Oracle Net Listener for the Gateway

  2. Stop and Start the Oracle Net Listener for the Gateway

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.

Syntax of listener.ora File Entries

The Oracle database communicates with the gateway using Oracle Net and any supported protocol adapters. The following is the syntax of the address on which the Oracle Net Listener listens using the TCP/IP protocol adapter:

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

Where:

Variable Description
host_name is the name of the machine on which the gateway is installed.
port_number specifies the port number used by the Oracle Net Listener. If you have other listeners running on the same machine, then the value of port_number must be different from the other listeners' port numbers.

To direct the Oracle Net Listener to start the gateway in response to incoming connection requests, add an entry to the listener.ora file.

Note:

You must use the same SID value in the listener.ora file and as the tnsnames.ora file which will be configured in the next step.
SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=g4drsrv)
      )
   )

Where:

Variable Description
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.
g4drsrv specifies the executable name of the Oracle Database Gateway for DRDA.

If you already have an existing Oracle Net Listener, then add 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=g4drsrv)
   )
)

See Also:

Oracle Database Net Services Administrator's Guide for information about changing the listener.ora file.

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.

Configure Two-Phase Commit

Run the appropriate script depending on the server you are connecting to:

Bind the DRDA Gateway Package

The product requires a package to be bound on the DRDA server. The gateway has an internal, stored procedure that must be used to create this package. The internal stored procedure is called from an Oracle database. Before this package can be bound on the DRDA server, the gateway initialization file must be correctly configured. Refer to Appendix D, "Configuration Worksheet for DRDA" for more information.

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.

Create Tables and Views for Data Dictionary Support

To enable data dictionary translation support, data dictionary tables and views have to be created on each non-Oracle system that you want to access through the gateway.

Perform the following steps to create the data dictionary tables and views using database native tools:

  1. Upgrade from a previous gateway release

    If you are upgrading from a previous version of the gateway then run the appropriate script to drop the old data dictionary definitions.

    • If connecting to DB2/UDB, then run

      ORACLE_HOME\dg4drda\install\db2udb\dropold.sql 
      
    • If connecting to DB2/OS390, then run

      ORACLE_HOME\dg4drda\install\db2\dropold.sql
      
    • If connecting to DB2/400, then run

      ORACLE_HOME\dg4drda\install\as400\dropold.sql
      
  2. Create the data dictionary tables

    Run the appropriate script to create the data dictionary tables.

    • If connecting to DB2/UDB, then run

      ORACLE_HOME\dg4drda\install\db2udb\g4ddtab.sql 
      
    • If connecting to DB2/OS390, then run

      ORACLE_HOME\dg4drda\install\db2\g4ddtab.sql
      
    • If connecting to DB2/400, then run

      ORACLE_HOME\dg4drda\install\as400\g4ddtab.sql
      
  3. Create the data dictionary views

    Run the appropriate script to create the data dictionary views:

    • If connecting to DB2/UDB, then run

      For DB2/UDB V7:

      ORACLE_HOME\dg4drda\install\db2udb\g4ddvwu7.sql
      

      For DB2/UDB V8:

      ORACLE_HOME\dg4drda\install\db2udb\g4ddvwu8.sql   
      
    • If connecting to DB2/OS390 then run

      For DB2/OS390 V7 (RACF security):

      ORACLE_HOME\dg4drda\install\db2\g4ddvwr7.sql
      

      For DB2/OS390 V7 (DB2 security):

      ORACLE_HOME\dg4drda\install\db2\g4ddvws7.sql
      

      For DB2/OS390 V8 (RACF security):

      ORACLE_HOME\dg4drda\install\db2\g4ddvwr8.sql
      

      For DB2/OS390 V8 (DB2 security):

      ORACLE_HOME\dg4drda\install\db2\g4ddvws8.sql
      
    • If connecting to DB2/400, then run

      For DB2/400 V5.1:

      ORACLE_HOME\dg4drda\install\as400\g4ddvw51.sql   
      

      For DB2/400 V5.2:

      ORACLE_HOME\dg4drda\install\as400\g4ddvw52.sql   
      

Grant Authority to the DRDA Package

To grant all users access to use the ORACLE2PC table, run the appropriate script from the following:

Configure the Oracle Database for Gateway Access

Before you use the gateway to access DRDA data you must configure the Oracle database to enable communication with the gateway over Oracle Net.

To configure the Oracle database you must add connect descriptors to the tnsnames.ora file. By default, this file is in $ORACLE_HOME/network/admin, where $ORACLE_HOME is the directory in which the Oracle database is installed. You cannot use the Oracle Net Assistant or the Oracle Net Easy Config tools to configure the tnsnames.ora file. You must edit the file manually.

A sample of the tnsnames.ora entry (tnsnames.ora.sample) is available in the ORACLE_HOME\dg4drda\admin directory where ORACLE_HOME is the directory under which the gateway is installed.

See Also:

Oracle Database Administrator's Guide for information about editing the tnsnames.ora file.

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:

Table 14-2 Gateway Parameters for tnsnames.ora File

Variable Description

connect_descriptor

is the description of the object to connect to as specified when creating the database link, such as dg4drda.

Check the sqlnet.ora file for the following parameter setting:

names.directory_path = (TNSNAMES)

Note: The sqlnet.ora file is typically stored in ORACLE_HOME\network\admin.

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 Oracle Net Listener that is listening for the gateway. The Oracle Net Listener's port number can be found in the listener.ora file used by the Oracle Net Listener. See "Syntax of listener.ora File Entries".

gateway_sid

specifies the SID of the gateway and matches the SID specified in the listener.ora file of the Oracle Net Listener that is listening for the gateway. See "Configure Oracle Net Listener for the Gateway" for more information.

(HS=OK)

specifies that this connect descriptor connects to a non-Oracle system.


Create Database Links

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

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

To access the DRDA server, you must create a database link. A public database link is the most common of database links.

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

Where:

Variable Description
dblink is the complete database link name.
tns_name_entry specifies the Oracle Net connect descriptor specified in the tnsnames.ora file that identifies the gateway

After the database link is created you can verify the connection to the DRDA database, as follows:

SQL> SELECT * FROM DUAL@dblink;

Configure the Gateway to Access Multiple DRDA Databases

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

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

Note:

If you have multiple gateway SIDs for the same DRDA 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 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;