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 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:
for DB2/OS390: choose dg4drda\install\db2
for DB2/400: choose dg4drda\install\as400
for DB2/UDB: choose dg4drda\install\db2udb
Perform the following tasks to configure the gateway initialization parameter file
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.
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 namedinitsid.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.
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.
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 |
|---|---|
|
|
Used to disable stored procedure support for DRDA Servers on which the gateway does not support stored procedures |
|
|
Defines the package Isolation Level |
|
|
Specifies the database SID name and must be set to the gateway SID |
|
|
Used for data query optimization |
|
|
Defines the package collection ID |
|
|
Defines the name of the package |
|
|
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. |
|
|
Defines the maximum number of concurrent |
|
|
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".
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
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:
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.
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 thetnsnames.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 thelistener.ora file.You must stop and restart the Oracle Net Listener to initiate the new settings, as follows:
From the Start menu, select Settings, Control Panel and then select Services.
Select the Oracle Net Listener service for the gateway.
If the service is already running, click Stop to stop it.
Click Start to start or restart the service.
Run the appropriate script depending on the server you are connecting to:
If connecting to DB2/UDB, the perform the following steps on the DRDA server platform to create the ORACLE2PC table:
Log in to the system where DB2/UDB is running.
Check that you have the ability to address the DB2/UDB instance where the ORACLE2PC table will reside.
Copy the files from the ORACLE_HOME\dg4drda\install\db2udb directory:
o2pc.sql (SQL script for creating the table)
o2pcg.sql (SQL script for granting package access to PUBLIC)
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 haveCONNECT, CREATETAB, and BINDADD authority to be able to connect to the database, to create the table, and to create the package.For more information, refer to "Configuring the DRDA Server for DB2/UDB (Universal Database)".
Create the ORACLE2PC table:
$ db2 -tf o2pc.sql
Commit the transaction:
$ db2 'COMMIT'
Optionally, verify that the table was created under the correct user ID:
$ db2 'LIST TABLES FOR USER' $ db2 'COMMIT'
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
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.
Before binding the DRDA gateway package, perform the following steps:
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".
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.
Use SQL*Plus:
> sqlplus system/manager
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'
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.
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:
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
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
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
To grant all users access to use the ORACLE2PC table, run the appropriate script from the following:
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
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 thetnsnames.ora file.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 |
|---|---|
|
|
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. |
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;
See Also:
Oracle Database Administrator's Guide and Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about using database links.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 and db3 on a host with IP Address 204.179.79.15 are being added.
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.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.
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.
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.
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;