3 Installation and Configuration of Oracle Database Provider for DRDA

Updating the drdaas.ora Configuration File

Typically, no additional parameters are needed beyond those specified during installation. Refer to the section "Configuration Parameters" for a list of parameters and their options.

In more advanced scenarios, it may be necessary to specify more than one DATA_PORT parameter or configure more than one RDB_MAP entry. Still more complex installations may require multiple Oracle Database Provider for DRDA instances.

Installing Database Objects

There are two sets of database objects that must be installed: Global Objects and Per-User Objects.

ThirdPartyTranslator.translateSQL(oracle.sql.CLOB,oracle.sql.CLOB[])
ThirdPartyTranslator.translateError(int,int[],java.lang.String[])

connect / as sysdba
@$ORACLE_HOME/drdaas/admin/drdasqtt_translator_setup.sql

Enter schema in which the SQL Translator Interface Package will be created as well
as into which the third-party SQL translator will be loaded (usually SYSIBM).
SQL Translator Interface Package Schema:SYSIBM

Enter unqualified name of the SQL Translator Interface Package
SQL Translator Interface Package Name:DBTooIntPkg

Enter schema in which the Translation Profile will be created:
Translation Profile Schema:TRANS_ADMIN

Enter the unqualified name of the translation profile:
Translation Profile Name:MyDBTooTransProfile

Enter the "language" type of the translator: C, java, etc
Translator Language:JAVA

Enter the path names of the third-party SQL Translator objects;
(All objects must be located under the "rdbms/" directory,
for example: "rdbms/drdaas/jlib/objecta.jar").

Enter all path qualified objects, one per prompt, up to 10.

Enter "" for all remaining object prompts.
SQL Translator object#1: rdbms/drdaas/jlib/ThirdPartyDB2Translator.class
SQL Translator object#2: ""
...
SQL Translator object#10: ""

Enter the signature for the entry for 'translateSQL' in one of the
previously entered SQL Translator objects:
Entry for
 translateSQL:ThirdPartyTranslator.translateSQL(oracle.sql.CLOB,oracle.sql.CLOB[])

Enter the signature for the entry for 'translateError' in one of the
previously entered SQL Translator objects callout for
 translateError:ThirdPartyTranslator.translateError(int,int[],java.lang.String[])

Configuration File: drdaas.ora

The file drdaas.ora defines the instances of Oracle Database Provider for DRDA. This file is composed of initialization parameters that define the instances of the Application Server.

The file drdaas.ora may be custom created. However, it may also be created at installation time from questions posed by the Installer and from user input.

The drdaas.ora configuration file must be located in the Oracle Home, under the product administration directory.

The default location is: $ORACLE_HOME/drdaas/admin.

Note that parameters that are qualified by the instance name apply only to that specific instance. Parameters that are not qualified by an instance name apply to all instances specified in the file.

# Example pre-configured instance named "drdaas"
# defines a single port and an rdb map that uses
# the local database instance accessed through
# the ORACLE_SID environmental variable.
drdaas.DATA_PORT = 10.0.0.1:1446
drdaas.RDB_MAP = RDB(DB2DS4M)->ORACLE_SID
#
# Example instance using a single port and a single rdb map
drdasingle.DATA_PORT = 10.0.0.1:1546
drdasingle.RDB_MAP = RDB(DB2DSN1)->TNS(ora101)
#
# Example instance using multiple rdb mappings
drdamulti.DATA_PORT = 10.0.0.1:2446
drdamulti.RDB_MAP = RDB(DB2DSN1)->TNS(ora101)
drdamulti.RDB_MAP = RDB(DB2DSN2)->TNS(ora102)
drdamulti.RDB_MAP = RDB(DB2DSN3)->TNS(ora103)
#
# global section affects all instances unless overridden
PROTOPROC_TRACE="ALL ERROR"

Administrator Role

Users who must perform administrative functions must have the DRDAAS_ADMIN_ROLE role. This enables privilege grants on a specific DRDA package, and assigning package attributes (SQL translation profile name).

connect sys as sysdba
grant DRDAAS_ADMIN_ROLE to DRDAADMIN;

For installations that do not have the default role ALL, have several default roles for users, such as CONNECT or RESOURCE, and add the add DRDAAS_ADMIN_ROLE role to the default list, as described in Example 3-10:

alter user DRDAADMIN default role CONNECT, DRDAAS_ADMIN_ROLE;

The DRDAAS_ADMIN_ROLE role is not meant for users who must use the DRDA packages. They should have the DRDAAS_USER_ROLE assignment, instead, as demonstrated in Example 3-9.

Administration is mainly concerned with granting and revoking of privilege to users, setting attributes on packages, and dropping packages.

Users who create packages, or are designated as owners of a package, have implicit authority over that package and may grant access to others. For example, the package owner may grant RUN privileges to any number of users. An owner may also set package attributes and drop the package.

However, in order to bind a package initially, a user must have BIND privilege, either for any package in a collection, or specifically for that package. Only users who have the DRDAAS_ADMIN_ROLE role may grant authorization to users for packages that are not already bound, or are not created or owned by that user. See "DRDA Package Authorization" for information on how to grant access to a package and how to set package attributes.

Another primary responsibility of an administrator is to clean old or unused packages from the system. A list of all packages may be found by querying table ALL_DRDAASPACKAGE:

SELECT * from ALL_DRDAASPACKAGE;

connect DRDAADM/password
execute DBMS_DRDAAS_ADMIN.DROP_PACKAGE('ORACLE','MYPACKAGE);
commit;

connect DRDAUSR2/password
execute DBMS_DRDAAS.DROP_PACKAGE('ORACLE','MYPACKAGE);
commit;

DRDA Package Authorization

The DRDA administrator must perform these functions before supplying the user with the DRDA package name and (optionally) the SQL translation profile name.

Only a DRDA administrator may grant access to specific DRDA packages. Example 3-7 demonstrates how to grant the BIND, DROP and EXECUTE privileges to user DRDAUSRx for package ORACLE.MYPACKAGE.

connect DRDAADM/password

Rem Grant BIND on any package in collection ORACLE to DRDAUSR
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.BIND_PRIVILEGE, 'ORACLE', '*', 'DRDAUSR');

Rem Grant BIND on package ORACLE.MYPACKAGE to user DRDAUSR2
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.BIND_PRIVILEGE, 'ORACLE', 'MYPACKAGE', 'DRDAUSR2');

Rem Grant EXECUTE on package ORACLE.MYPACKAGE to PUBLIC
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.EXECUTE_PRIVILEGE, 'ORACLE', 'MYPACKAGE', 'PUBLIC');

Rem Grant SET on package ORACLE.MYPACKAGE to user DRDAUSR3
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.SET_PRIVILEGE, 'ORACLE', 'MYPACKAGE', 'DRDAUSR3');

Rem Grant DROP on any package in collection ORACLE to user DRDAUSR3
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.DROP_PRIVILEGE, 'ORACLE', '*', 'DRDAUSR3');

Rem Grant ALL on any package in collection NULLID to user DRDAUSR3
execute DBMS_DRDAAS_ADMIN.GRANT_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, 'NULLID', '*', 'DRDAUSR3');

Rem Revoke BIND on package ORACLE.NOTYOURPKG from user DRDAUSR3
execute DBMS_DRDAAS_ADMIN.REVOKE_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.BIND_PRIVILEGE, 'ORACLE', 'NOTYOURPKG', 'DRDAUSR3');

Rem Revoke ALL on any package in Collection OTHER from user DRDAUSR4
execute DBMS_DRDAAS_ADMIN.REVOKE_PRIVILEGE(
     DBMS_DRDAAS_ADMIN.ALL_PRIVILEGE, 'OTHER', '*', 'DRDAUSR4');

The DRDA Administrator may also designate a SQL translation profile name to associate with the DRDA package. Example 3-8 demonstrates how to set the profile name to DB2ZOS.

connect DRDAADM/password
 
Rem Set the DB2ZOS profile name for "any" package in collection ORACLE
DBMS_DRDAAS_ADMIN.SET_PROFILE( 'ORACLE', '*', 'DB2ZOS' );
 
Rem Set the MYDB2ZOS profile name for package ORACLE.MYPACKAGE
DBMS_DRDAAS_ADMIN.SET_PROFILE( 'ORACLE', 'MYPACKAGE', 'MYDB2ZOS' );
 
Rem Deleting the profile name for package ORACLE.MYPACKAGE
execute DBMS_DRDAAS_ADMIN.SET_PROFILE( NULL, 'ORACLE', 'MYPACKAGE' );
 
Rem Deleting the profile name for "any" package in collection ORACLE
execute DBMS_DRDAAS_ADMIN.SET_PROFILE( NULL, 'ORACLE', '*' );

User Role

Each user who accesses the database through DRDA must have the DRDAAS_USER_ROLE user role as a default.

connect sys as sysdba
grant DRDAAS_USER_ROLE to DRDAUSR;

Users commonly have the default role ALL, which immediately enables all granted roles. If a user has explicit default roles, they must also have the DRDAAS_USER_ROLE role, as described in Example 3-10:

Note that failing to specify the complete list of default roles may prevent the user from connecting to the database, or form being able to address certain resources implicitly. Refer to Oracle® Database SQL Language Reference and Oracle Database Security Guide.

alter user DRDAUSR default role CONNECT, RESOURCE, DRDAAS_USER_ROLE;

Removing the Database Objects

The following steps remove all Oracle Database Provider for DRDA objects from the database, and drop the user-created tablespace.

To remove Database objects:

1. Change directory to $ORACLE_HOME/rdbms/admin.

   > cd $ORACLE_HOME/rdbms/admin
   

2. Connect to the database using the SYSDBA option.

   connect / as sysdba
   

3. Run the removal script.

   catnodrdaas.sql
   

   See catnodrdaas.sql in Appendix A, "Scripts for Creating and Maintaining Oracle Database Provider for DRDA".

4. Drop the user-created tablespace sysibm and its contents.

   > drop tablespace sysibm;
   

See Oracle® Database SQL Language Reference for DROP TABLESPACE options.

Uninstalling Oracle Database Provider for DRDA software

To uninstall Oracle Database Provider for DRDA software, you must use the Oracle Universal Installer with the -deinstall option. You may choose specify path to Oracle Home using the -home option, or choose Oracle Home when Oracle Universal Installer is running.See further instructions on uninstalling Oracle software in Oracle® Database Installation Guide.

To uninstall Oracle Database Provider for DRDA software:

1. In Oracle Universal Installer, click Installed Products.

2. Select the desired ORACLE_HOME.

   Either select the whole ORACLE_HOME, or open ORACLE_HOME navigation tree and select Oracle Database Provider for DRDA product software.

3) Click Remove.

DATA_PORT

This designates the DRDA data port used by this instance, and is represented by an Internet Address and Port Number.

Default Value

There is no default port number; an explicit port number must be specified. Oracle recommends using 1446.

Allowable Values

A valid, unallocated TCP/IP network port number, optionally prefixed with a specific host name or IP address associated with a defined network interface on the local machine.

Syntax

DATA_PORT = {host_name|ip_address:}number

Usage Example

DATA_PORT = 10.0.0.1:1446

RDB_MAP

This string parameter maps relational database names, as passed in the DRDA ACCRDB command object, to Oracle TNS entries, or to the locally addressable Oracle instance. This parameter may contain several occurrences of a map entry.

This has no default value.

A list of optional values includes the following:

• tns_name_entry corresponds to a TNS entry in the local tnsnames.ora configuration file.

• tns_entry is a fully-formed TNS descriptor string. It may be used instead of a TNS name entry.

• oracle_sid uses the $ORACLE_SID environment variable value that is set prior to starting an Oracle Database Provider for DRDA instance.

Note that the use of one or more occurrences of RDB_MAP determines a mode of compatibility with older application requesters. The default is a single, dedicated definition that connects to a single Oracle Database instance based on the ORACLE_SID environmental variable.

Allowable Values

A valid, unallocated TCP/IP network port number, optionally prefixed with a specific host name or IP address associated with a defined network interface on the local machine.

Syntax

RDB_MAP = RDB(rdb_name)->TNS(tns_name_entry)
RDB_MAP = RDB(rdb_name)->ORACLE_SID
RDB_MAP = RDB(rdb_name)->"tns_entry"

Usage Examples

RDB_MAP = RDB(DB2DSN1)->ORACLE_SID
RDB_MAP = RDB(DB2DSN2)->TNS(ora101)
RDB_MAP = RDB(DB2DSN3)->"(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.1)(PORT=1446))"

PROTOPROC_TRACE

This parameter designates the trace facility and level of detail for tracing of the DRDA Protocol Processor. All initial Oracle Database Provider for DRDA sessions run with this setting. The parameter consists of a value pair that represents facility and level values. Multiple values may be specified simultaneously if the value tuples are separated by a comma.

It also designates the initial level of trace under which all AS session threads execute. PROTOPROC_TRACE is a decimal number or textual designated equivalent.

The facility names used with PROTOPROC_TRACE parameter are as follows:

• TASK – Task-specific operations

• NET – Network-specific operations

• SQL – SQL-specific operations

• OCI – OCI resource operations

• MEM – Memory resource operations

• ALL – All facilities mentioned already

The values of PROTOPROC_TRACE level are additive. For example, setting ERROR(4) includes WARN(2) and INFO(1) messages. The following values are expected:

• 0 or NONE – No trace is generated; this is the default.

• 1 or INFO – Minimal trace is generated.

• 2 or WARN – Warning information is generated.

• 4 or ERROR – Error information is generated.

• 8 or ADMIN – Administration information is generated.

• 255 or ALL – All details are generated.

Default Value

0 or none

Allowable Values

Facility name followed by level.

Usage Example

PROTOPROC_TRACE="ALL ADMIN"
PROTOPROC_TRACE="TASK WARN, NET ADMIN, MEM INFO"