14 Configuring Oracle Database Gateway for DRDA
After installing the gateway, perform the following tasks to configure Oracle Database Gateway for DRDA:
SQL scripts are provided to perform steps such as creating the HS_TRANSACTION_LOG
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 UDB for Linux, UNIX, and Windows), 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.
SQL scripts are located in the dg4db2/admin
directory. Appropriate platform scripts are designated by having the DB2 platform identifiers (eg: "zos", "as400" and "luw") and version specific numbers (eg: vw7, vw8) in their file names.
Configure the Gateway 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
Tailor the parameter file with additional parameters as needed. Refer to Initialization Parameters for a list of supported initialization parameters. Also refer to Security Considerations for security aspects to tailoring the parameter file.
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:
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
\dg4db2\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 syntax of the address on which the Oracle Net Listener listens using the TCP/IP protocol adapter is as follows:
LISTENER= (ADDRESS= (PROTOCOL=TCP) (HOST=host_name) (PORT=port_number))
Where:
Variable | Description |
---|---|
|
is the name of the machine on which the gateway is installed. |
|
specifies the port number used by the Oracle Net Listener. If you have other listeners running on the same machine, then the value of |
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=dg4db2) ) )
Where:
Variable | Description |
---|---|
|
specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the |
|
specifies the Oracle home directory where the gateway resides. |
|
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=dg4db2) ) )
See Also:
Oracle Database Net Services Administrator's Guide for information about changing the listener.ora
file.
Configure Two-Phase Commit
Support for Two-Phase Commit requires running the ORACLE_HOME\dg4db2\admin\dg4db2_tx.sql
script on the DB2 server. This script will create objects used by the gateway for Two-Phase Commit. Edit the script and replace the default recover account schema ("RECOVER
") with the account name specified for the HS_FDS_RECOVERY_ACCOUNT
initialization parameter. Refer to Initialization Parameters for more details.).
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:
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 tnsnames.ora
entry (tnsnames.ora.sample
) is available in the ORACLE_HOME
\dg4db2\admin
directory where ORACLE_HOME
is the directory under which the gateway is installed.
See Also:
Oracle Database Net Services 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-1 Oracle Database Gateway for DRDA Parameters for tnsnames.ora File
Variable | Description |
---|---|
|
is the description of the object to connect to as specified when creating the database link, such as Check the
Note: The |
TCP |
is the TCP protocol used for TCP/IP connections. |
|
specifies the machine where the gateway is running. |
|
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 |
|
specifies the SID of the gateway and matches the SID specified in the |
|
specifies that this connect descriptor connects to a non-Oracle system. |
Configuring tnsnames.ora for Multiple Listeners
To ensure higher availability, you can specify multiple listeners within the connect descriptor.
connect_descriptor= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=host_name_1) (PORT=port_number_1) ) (ADDRESS= (PROTOCOL=TCP) (HOST=host_name_2) (PORT=port_number_2) ) (CONNECT_DATA= (SID=gateway_sid)) (HS=OK))
This indicates that, if the listener for host_name_1
and port_number_1
is not available, then the second listener for host_name_2
and port_number_2
will take over.
See Also:
Oracle Database Net Services Administrator's Guide for information about editing the tnsnames.ora
file.
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 |
---|---|
|
is the complete database link name. |
|
specifies the Oracle Net connect descriptor specified in the |
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 for more information about using database links.
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.
-
The gateway is configured for one DRDA database named
db1
. -
Two DRDA databases named
db2
anddb3
on a host with IP Address 204.179.79.15 are being added.
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:
-
dg4db22
for the gateway accessing databasedb2
. -
dg4db23
for the gateway accessing databasedb3
.
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
\dg4db2\admin\initdg4db2.ora
, twice, naming one with the gateway SID for db2
and the other with the gateway SID for db3
:
> cd ORACLE_HOME\dg4db2\admin
> copy initdg4db2.ora initdg4db22.ora
> copy initdg4db2.ora initdg4db23.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=dg4db2) (ORACLE_HOME=oracle_home_directory) (PROGRAM=dg4db2) ) (SID_DESC= (SID_NAME=dg4db22) (ORACLE_HOME=oracle_home_directory) (PROGRAM=dg4db2) ) (SID_DESC= (SID_NAME=dg4db23) (ORACLE_HOME=oracle_home_directory) (PROGRAM=dg4db2) ) )
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:
- From the Start menu, select Settings, Control Panel and then select Services.
- Select the Oracle Net Listener service for the gateway.
- Click Stop.
- 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=dg4db2)) (HS=OK)) new_db2_using=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (PORT=port_number) (HOST=host_name)) (CONNECT_DATA= (SID=dg4db22)) (HS=OK)) new_db3_using=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (PORT=port_number) (HOST=host_name)) (CONNECT_DATA= (SID=dg4db23)) (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 dg4db22
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 dg4db23
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;