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 GatewayPackage

  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:

14.1 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

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

14.1.2 Customize the Initialization Parameter File

To configure the host for the Oracle Database Gateway for DRDA, you must tailor the parameter files for your installation. Perform the following steps:

  1. Choose the initsid.ora file

    The initsid.ora gateway initialization file defines the operating parameters for the gateway. Samples (tailored for each type of DRDA Server) are provided as a starting point for tailoring to your particular installation. The samples are stored in the $ORACLE_HOME/dg4drda/admin directory. The following is a list of the initialization files for various DRDA Server platforms:

    • For DB2/OS390: initDB2.ora

    • For DB2/UDB: initDB2UDB.ora

    • For DB2/400: initAS400.ora

    Choose a sample initialization file and copy it, within the same directory, using the following naming convention:

    initsid.ora
    

    where sid is the chosen gateway SID. For example, if the chosen gateway SID were DRDA, then the initialization file would be named initDRDA.ora.

  2. 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. Attention should be given to the following DRDA and HS parameters. Attention should also be given to the security aspects of the initialization file. Chapter 15, "Security Considerations", contains details concerning encryption of passwords that would otherwise be embedded in the initialization file. See Appendix C, "Initialization Parameters", for a description of the following parameters:

    • DRDA_CONNECT_PARM

    • DRDA_PACKAGE_COLLID

    • DRDA_PACKAGE_NAME

    • DRDA_PACKAGE_OWNER

    • DRDA_REMOTE_DB_NAME

    • HS_DB_NAME

    • HS_DB_DOMAIN

    • FDS_CLASS

14.2 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

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

14.2.1.1 Syntax of listener.ora File Entries

The Oracle database communicates with the gateway using Oracle Net and any supported protocol adapters. The following 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 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 are already running a Oracle Net 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=g4drsrv)
   )
)

See Also:

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

14.2.2 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. Set the PATH environment variable to $ORACLE_HOME/bin where $ORACLE_HOME is the directory in which 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
    

    Table 14-1 specifies which parameter value to use for the different platforms:

    Table 14-1 Parameter Values for UNIX Based Platforms

    Platform Parameter Value

    Solaris (SPARC) 64 bit

    LD_LIBRARY_PATH=$ORACLE_HOME/lib

    HP-UX PA-RISC

    SHLIB_PATH=$ORACLE_HOME/lib

    Linux x86, and Linux x86 64 bit

    LD_LIBRARY_PATH=$ORACLE_HOME/lib

    AIX

    LIBPATH=$ORACLE_HOME/lib


  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 a partial output from a lsnrctl status check:

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

In this example, the service name is dg4drda which is the default SID value assigned during installation.

Note:

You must use the same SID value in the tnsnames.ora file and the listener.ora file.

14.3 Configure Two-Phase Commit

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

  • If connecting to DB2/UDB, then perform the following steps on the DRDA server platform, to create the ORACLE2PC table:

    1. Log into the machine where DB2/UDB is running.

      Check that you have the ability to address the DB2/UDB instance where the ORACLE2PC table will reside.

    2. Copy the files from the $ORACLE_HOME/dg4drda/install/db2udb directory.

      • o2pc.sh (Sample shell script for performing the table creation)

      • o2pc.sql (SQL script for creating the table)

      • o2pcg.sql (SQL script for granting package access to PUBLIC)

    3. Connect to the database using the user ID that you will use for binding the package:

      $ db2 'CONNECT TO database USER userid USING password' 
      

      Note:

      The user ID must have CONNECT, CREATETAB, and BINDADD authority to be able to connect to the database, create the table, and create the package.

      For more information, refer to "Configuring the DRDA Server for DB2/UDB(Universal Database)".

    4. Create the ORACLE2PC table:

      $ db2 -tf o2pc.sql
      
    5. Commit the transaction:

      $ db2 'COMMIT'
      
    6. Verify that the table was created.

      Optionally, verify the table was created under the correct user ID:

      $ db2 'LIST TABLES FOR USER'
      $ db2 'COMMIT'
      
    7. Disconnect from the session:

      $ db2 'DISCONNECT CURRENT'
      
  • If connecting to DB2/400, then run the following SQL script on the DRDA server platform using a database native tool, to create your ORACLE2PC table:

    $ORACLE_HOME/dg4drda/install/as400/o2pc.sql 
    
  • If connecting to DB2/OS390, then run the following SQL script on the DRDA server platform using a database native tool, to create your ORACLE2PC table:

    $ORACLE_HOME/dg4drda/install/db2/o2pc.sql 
    

14.4 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 invoked 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 C, "Initialization Parameters" for more information.

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

14.4.2 DRDA Gateway Package Binding Steps

Perform the following 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. Execute 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 and re-execute the bind procedure above.

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

14.6 Grant Authority to the DRDA Package

To grant authority to the package, run the appropriate script on the non-oracle system:

  • If connecting to DB2/UDB, then run

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

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

    $ORACLE_HOME/dg4drda/install/as400/o2pcg.sql
    

14.7 Configure the Oracle Database for Gateway Access

Before you use the gateway to access DB2 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 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.

See Also:

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

14.7.1 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:

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.

14.8 Create Database Links

Any Oracle client connected to the Oracle database can access DB2 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;

See Also:

Oracle Database Administrator's Guide and Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about using database links.

14.9 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:

  • The gateway is installed and configured with the default SID of dg4drda

  • The ORACLE_HOME environment variable is set to the directory where the gateway is installed

  • The gateway is configured for one DRDA database named db1

  • Two DRDA databases named db2 and db3 on a host with IP Address 204.179.79.15 are being added

14.9.1 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$ cp initdg4drda.ora initdg4drda2.ora$ cp 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.

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

14.9.3 Multiple DRDA Databases Example: Stopping and Starting the Oracle Net 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

14.9.4 Multiple Databases Example: Configuring Oracle Database for Gateway Access

14.9.4.1 Configuring Oracle Net 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 DRDA 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=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.

14.9.5 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;