Skip Headers
Oracle® Application Server for DRDA User's Guide
12c Release 1 (12.1) for Linux x86-64

E27316-06
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

3 Installation, Configuration, and Administration of Oracle Application Server for DRDA

This chapter discusses installation, configuration, and administration of Oracle Application Server for DRDA.

This chapter contains these topics:

Installing Oracle Application Server for DRDA

Installation involves starting the Oracle Universal Installer, entering the Oracle home path, and selecting "Oracle Application Server for DRDA" product to be installed. The installation will ask for several initial configuration items in an Interview panel for the product.

Note that the following procedure creates the drdaas.ora configuration file in the directory $ORACLE_HOME/drdaas/admin/. See "Configuration File: drdaas.ora".

To install Oracle Application Server for DRDA:

  1. Start Oracle Universal Installer.

  2. Enter the path of an existing ORACLE_HOME, or a new path for a stand-alone installation.

  3. [Optional] Enter the ORACLE_HOME name.

  4. Select Oracle Application Server for DRDA for installation.

  5. In the Interview panel, enter the following information:

    • Oracle Application Server for DRDA listener host name and/or IP address

      Specify host name or IP address of the network interface where Oracle Application Server for DRDA is the listener.

      Default is an empty string.

    • Oracle Application Server for DRDA listener port number

      Specify the port number of Oracle Application Server for DRDA Listener.

      Default is 1446.

    • Oracle Application Server for DRDA RDB map name

      Specify the external relational database name that the client applications use as a location qualifier.

      Default is DRDAAS.

    • Oracle Database connection descriptor

      Specify the connection descriptor to use to connect to the Oracle Database. Valid values are:

      • TNS(tns_name_sentry), such as TNS(orcl)

      • oracle_sid or ORACLE_SID

        This accesses the local Oracle Database before Oracle Application Server for DRDA starts, based on the setting of the environment variable $ORACLE_SID.

      • TNS-Descriptor

        For example:

        DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=drdaas))
        (CONNECT_DATA=(SERVICE_NAME=drdaas.us.oracle.com))); 
        

        Default is oracle_sid.

  6. Select Finish.

  7. At the command prompt, create a diagnostic directory:

    % mkdir -p $ORACLE_HOME/log/diag/dps
    

Configuring Oracle Application Server for DRDA

To configure Oracle Application Server for DRDA, you must update the drdaas.ora configuration file with any necessary information.

Additionally, you must install the objects that depend on Oracle Application Server for DRDA product in all Oracle Database instances that use Oracle Application Server 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 Application Server for DRDA instances.

DATA_PORT Considerations

Additional DATA_PORT entries may be specified with different host name or IP addresses, and unallocated network port numbers. This is called a Multiplexed Instance configuration.

RDB_MAP Considerations

Additional RDB_MAP entries may be specified to add other map entries for converting between third-party relational databases and Oracle.

For some DRDA clients, such as the IBM DB2 Database for z/OS, the user must configure only one RDB_MAP entry for each Oracle Application Server for DRDA instance. This is called a Dedicated Instance configuration.

Oracle Application Server for DRDA Instance Considerations

It may be necessary to define more than one Oracle Application Server for DRDA Instance to accommodate an environment that contains both types of DRDA clients. Therefore, Oracle Application Server for DRDA product supports the definition of multiple instance configurations in the same drdaas.ora configuration file.

Installing Database Objects

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

Global Objects

Each instance of Oracle Database used in an Oracle Application Server for DRDA configuration must install Oracle Application Server for DRDA specific objects. This involves the following procedures:

  • Creating a SYSIBM tablespace

  • Installing Oracle Application Server for DRDA catalogs

  • Installing DB2 SQL translator

  • Designate Oracle Application Server for DRDA administrative role

See Chapter 11, "Security and Storage Considerations for Oracle Application Server for DRDA".

To create a SYSIBM tablespace:

  1. Connect to the database; this example uses SYSDBA privileges, but they are not necessary.

    > connect SYS as SYSDBA
    
  2. Create the tablespace SYSIBM.

    > create tablespace SYSIBM datafile 'sysibm01.dbf' size 70M 
      reuse extent management local segment space management auto online;
    

    This creates tablespace SYSIBM in the directory specified by the parameter DB_CREATE_FILE_DEST. This is discussed in more detail in section "SYSIBM Tablespace".

To install Oracle Application Server for DRDA catalogs:

  1. Change directory to $ORACLE_HOME/rdbms/admin

    $ cd $ORACLE_HOME/rdbms/admin
    
  2. Connect to the database with SYSDBA privileges.

    > connect SYS as SYSDBA
    
  3. Invoke the following SQL script:

    > @catdrdaas.sql
    

    See catdrdaas.sql in Appendix A, "Scripts for Creating and Maintaining the Environment for Oracle Application Server for DRDA".

  4. Invoke the following SQL script:

    > @prvtdpsadzoscat.plb
    

To install DB2 SQL translator:

  1. Change directory to $ORACLE_HOME/drdaas/admin

    $ cd $ORACLE_HOME/drdaas/admin
    
  2. Connect to the database with SYSDBA privileges.

    > connect SYS as SYSDBA
    
  3. Invoke the following SQL script, and answer its prompts:

    > @drdasqtt_translator_setup.sql
    

    The script drdasqtt_translator_setup.sql is demonstrated in Example 3-1.

To designate Oracle Application Server for DRDA administrative role:

  1. Designate one or more user IDs to be an Oracle Application Server for DRDA administrator. This role may set the access authority for DRDA packages and associated DRDA package attributes. Oracle user SYSTEM may be used, but the privilege may be granted to any user who performs the functions of DRDA administrator.

  2. Grant initial DRDA package binding authority. Invoke the following SQL script and answer the prompts:

    > @drdapkg_db2.sql
    
    SQL> Prompt Enter the OracleID under which the initial package BINDs will be made
    SQL> Use quotes (') if needed.
    SQL> Accept OracleID 
    DRDAUSR
    
    SQL> Enter default collection ID for package binding (usually NULLID)
    SQL> Use quotes (') if needed.
    SQL> Accept DefaultCollection
    NULLID
    

See the "Database Roles in Oracle Application Server for DRDA" and "DBMS_DRDAAS_ADMIN Package".

SQL Translation Profile

To facilitate correct interpretation of DRDA-based application SQL from it's native DB2 dialect to Oracle, the user must create a SQL Translation Profile.

Note that to perform these steps, the user must have DRDAAS_USER_ROLE, as described in "Authorizing Oracle Application Server for DRDA".

A DB2 SQL Translator must be loaded into the database; see "Global Objects". The user may create a translation profile using this translator.

SQL Translation Profiles are managed on a per-user basis. In contrast, DRDA packages are managed on the application basis. As a result, only one SQL translation profile name may be associated with a specific DRDA package. The same SQL Translation Profile may be also associated with many packages; for consistency, the same SQL Translation Profile should be associated with each defined package. See Oracle® Database Migration Guide for details of SQL translation.

To create an additional translation profile, DRDA users must request a profile name from the DRDA administrator and then invoke the drdasqtt_translator_setup.sql script, in $ORACLE_HOME/drdaas/admin/ directory. Example 3-1 demonstrates how to create a SQL Translation Profile for profile name DB2ZOS; this code creates the template of a translation profile.

Example 3-1 Creating a SQL Translation Interface Package and a Translation Profile

This example creates a SQL Translator Interface Package SYSIBM.DBTooIntPkg, and a SQL Translation Profile TRANS_ADMIN.MyDBTooTransProfile. It assumes that the third-party SQL translator is in JAVA, and that it appears entirely within an object ThirdPartyDB2Translator.class in the rdbms/drdaas/jlib directory.

The following two lines describe the signatures of the two translator methods within the third-party object:

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

These signatures determine the method Oracle calls to translate both SQL text and Oracle Error codes. The method translateSQL()has two arguments: a CLOB for the original SQL text, and a CLOB for the CLOB output from the SQL translator. The second method may be ignored.

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 names of the third-party SQL Translator objects
They should be available via rdbms/drdaas/jlib/..
If there is more than one object, enclose the entire set in quotes.
SQL Translator object(s):ThirdPartyDB2Translator.class

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[])

Additional translations may be added, changed, or removed as needed. Please refer to the Oracle® Database Migration Guide for details.

Configuration File: drdaas.ora

The file drdaas.ora defines the instances of Oracle Application Server 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 3-2 Sample configuration file, drdaas.ora

# 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"

Authorizing Oracle Application Server for DRDA

Oracle users must have the appropriate Oracle Application Server for DRDA role in order to access Oracle Application Server for DRDA catalogs and specific DRDA packages. See "Storage in Oracle Application Server for DRDA".

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

Example 3-3 Granting the DRDAAS_ADMIN_ROLE

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:

Example 3-4 Adding DRDAAS_ADMIN_ROLE to Default Values

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;

Example 3-5 Dropping package ORACLE.MYPACKAGE, as Administrator

This function should be performed by a user with DRDA administrator role.

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

Example 3-6 Dropping package ORACLE.MYPACKAGE, as User

This function should be performed by a user with DRDA user role. This operation fails if user DRDAUSR2 does not own package ORACLE.MYPACKAGE, if the user is not the creator of this package, or if the user has no DROP privilege for this package.

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.

Example 3-7 Granting and Revoking a User's Package Privileges

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.

Example 3-8 Setting and Deleting Translation Profile Name for a DRDA Package

connect DRDAADM/password
 
Rem Set the DB2ZOS profile name for "any" package in collection ORACLE
execute DBMS_DRDAAS_ADMIN.SET_PROFILE( 'DB2ZOS', 'ORACLE', '*' );
 
Rem Set the MYDB2ZOS profile name for package ORACLE.MYPACKAGE
execute DBMS_DRDAAS_ADMIN.SET_PROFILE( 'MYDB2ZOS', 'ORACLE', 'MYPACKAGE' );
 
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 DRDA user role as a default.

Example 3-9 Granting the DRDAAS_USER_ROLE

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.

Example 3-10 Adding DRDAAS_USER_ROLE to Default Values

alter user DRDAUSR default role CONNECT, RESOURCE, DRDAAS_USER_ROLE;

Uninstalling Oracle Application Server for DRDA

Full uninstall of Oracle Application Server for DRDA involves the removal of the Database objects and uninstall of Oracle Application Server for DRDA software. The product-dependent objects are removed, while the customer data remains intact.

Removing the Database Objects

The following steps remove all Oracle Application Server 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 the Environment for Oracle Application Server 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 Application Server for DRDA software

To uninstall Oracle Application Server 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 Application Server 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 Application Server for DRDA product software.

3) Click Remove.

Configuration Parameters

This section contains the description of parameters used in the configuration file.

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 Application Server 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 Application Server 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"