1/22

Contents

List of Tables

Title and Copyright Information

Preface

• Audience
• Related Documents
• Conventions

1 Introduction to Oracle Database Provider for DRDA

• What is Oracle Database Provider for DRDA?
• Release Information
• DB2 Client Applications
  • Remote DB2 Applications
  • Native DB2 Applications
• Usage Scenarios for Oracle Database Provider for DRDA

2 Architecture of Oracle Database Provider for DRDA

• Protocol Considerations
• Two-Phase Commit and Transaction Recovery
• Autonomy of Service
• Packages
• SQL Dialect

3 Installation and Configuration of Oracle Database Provider for DRDA

• About Installing Oracle Database Provider for DRDA
  • installing Oracle Database Provider for DRDA
• Configuring Oracle Database Provider for DRDA
  • Updating the drdaas.ora Configuration File
    • DATA_PORT Considerations
    • RDB_MAP Considerations
    • Oracle Database Provider for DRDA Instance Considerations
  • Installing Database Objects
    • About Global Objects
    • Creating a SYSIBM tablespace
    • Installing Oracle Database Provider for DRDA Catalogs
    • Installing DB2 SQL translator
    • Designating Oracle Database Provider for DRDA Administrative Role
  • SQL Translation Profile
    • Prerequisites for Creating a SQL Translation Profile
    • Creating a SQL Translation Interface Package and Translation Profile
  • Configuration File: drdaas.ora
• Authorizing Oracle Database Provider for DRDA
  • Administrator Role
    • Granting DRDAAS_ADMIN_ROLE
    • Adding DRDAA_ADMIN_ROLE
    • Dropping ORACLE.MYPACKAGE by Administrator
    • Dropping ORACLE.MYPACKAGE by User
    • Maintaining DRDA Packages
  • DRDA Package Authorization
    • Managing a User’s Package Privileges
    • Managing DRDA Package Translation Profile
  • User Role
    • Granting the DRDAAS_USER_ROLE
    • Adding DRDAAS_USER_ROLE
• Uninstalling Oracle Database Provider for DRDA
  • Removing the Database Objects
  • Uninstalling Oracle Database Provider for DRDA software
• Configuration Parameters
  • DATA_PORT
  • RDB_MAP
  • PROTOPROC_TRACE
  • PROTOPROC_OPTIONS

4 SQL Translation and Examples for Oracle Database Provider for DRDA

• Overview of SQL Translation Process
  • Implementing SQL Translation
  • Requirements for SQL Translation
• Specifics of Translating DB2-Specific SQL Syntax
  • DB2 Special Registers
  • DB2 SQL Functions and Procedures
  • DB2 Named Datatypes
  • DB2 Syntactic Statements
• SQL Translator Interface Package
  • About SQL Translator Interface Package
  • Creating a SQL Translator Interface Package
  • Granting EXECUTE Access to SQL Translator Interface Package
  • Creating a SQL Translation Profile
    • Granting Translation Authority Through Administrator Role
    • Granting Translation Authority Through User Role
    • Creating and Managing SQL Translation Profile
• Using Third-Party SQL Translators
  • Using a Third-Party SQL Translator, Loaded as a Single Object
  • Using a Third-Party SQL Translator, Loaded as Multiple Objects
• Using a Translator Management Script
• Verifying the SQL Translator Profile
• Altering the SQL Translation Profile

5 Administration and Customization of Oracle Database Provider for DRDA

• Migration Steps using Oracle Database Provider for DRDA
• Considerations for Using Oracle Database Provider for DRDA
• Prerequisites to Installing Oracle Database Provider for DRDA
• Administering DRDA Package Authority
• Migrating DB2 Data
• Retargeting the Application to Use Oracle Database
  • Re-targeting Native Applications
  • Re-targeting Remote Applications
• Translating SQL Statement and Typing Datatypes
  • Registering a SQL Substitution Statement
  • Registering an On-Demand Datatype Conversion

6 Diagnostics and Maintenance of Oracle Database Provider for DRDA

• Diagnostics for Oracle Database Provider for DRDA
• Maintaining Oracle Database Provider for DRDA

7 Datatype Support and Conversion in Oracle Database Provider for DRDA

• Overview of Datatype Conversion
• Numerical Range Considerations; General
  • Oracle NUMBER
  • FLOAT (IBM HEX or S390)
  • FLOAT (IEEE)
  • DECFLOAT
• Numerical Range Considerations, for COBOL Users
  • Constraining Oracle NUMBER
• Conversion between DRDA Datatypes to Oracle Datatypes
  • INTEGER
  • SMALLINT
  • BIGINT
  • float
  • DOUBLE PRECISION or FLOAT(b)
  • REAL or FLOAT(b)
  • DECIMAL(p,s)
  • DECIMAL(p,s) zoned
  • NUMERIC(p,s)
  • DECFLOAT(n=34)
  • DECFLOAT(n=16)
  • CHAR(n)
  • CHAR(n) for Bit Data
  • VARCHAR(n)
  • VARCHAR(n)
  • VARCHAR(n) for Bit Data
  • VARCHAR(n)
  • VARCHAR(n)
  • VARCHAR(n) for Bit Data
  • char(n+1)
  • char(n+1)
  • char(n) for Bit Data
  • VARGRAPHIC(n)
  • GRAPHIC(n)
  • VARGRAPHIC(n)
  • char(n) (Pascal L String)
  • char(n) for Bit Data (Pascal L String)
  • DATE
  • TIME
  • TIMESTAMP
  • (datalink)
  • BLOB
  • CLOB
  • DBCLOB
  • BLOB LOCATOR
  • CLOB LOCATOR
  • DBCLOB LOCATOR
  • boolean
  • BINARY(n)
  • VARBINARY(n)
  • XML
• Conversion of Oracle Datatype to DRDA
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • VARCHAR2(n)
  • LONG
  • LONG RAW
  • NVARCHAR2(n)
  • CHAR(n)
    • Shorter version
    • Longer Version
  • NCHAR(n)
    • Shorter version
    • Longer Version
  • UROWID
  • DATE
  • TIMESTAMP
  • TIMESTAMP WITH LOCAL TIME ZONE
  • TIMESTAMP(p) WITH TIME ZONE
  • RAW(n)
  • NUMBER and FLOAT
• Datatype Equivalence and Remapping
  • Applying Datatype Mapping
  • Using TYPEMAP in Queries
  • Using TYPEMAP in Functions
  • Oracle NUMBER TYPEMAP

8 Data Dictionary for Oracle Database Provider for DRDA

• Data Dictionary Emulation in Oracle Database Provider for DRDA
  • DB2 for z/OS
• Data Dictionary Views for Oracle Database Provider for DRDA
  • ALL_DRDAASPACKAGE Data Dictionary View
  • ALL_DRDAASPACKAUTH Data Dictionary View
  • ALL_DRDAASPACKSIDE Data Dictionary View
  • DBA_DRDAASPACKAGE Data Dictionary View
  • DBA_DRDAASPACKAUTH Data Dictionary View
  • DBA_DRDAASPACKSIDE Data Dictionary View
  • DBA_DRDAASPACKSTMT Data Dictionary View
  • DBA_DRDAASTRACE Data Dictionary View
  • USER_DRDAASPACKAGE Data Dictionary View
  • USER_DRDAASPACKAUTH Data Dictionary View
  • USER_DRDAASPACKSIDE Data Dictionary View
  • USER_DRDAASPACKSTMT Data Dictionary View
  • USER_DRDAASTRACE Data Dictionary View

9 Error Codes Support in Oracle Database Provider for DRDA

• Oracle Error Codes
  • Error Code Mapping, from Oracle to DRDA

10 Command-line Utility for Oracle Database Provider for DRDA

• Command-line Utility
  • START
  • STOP
  • STATUS
  • TRACE
  • PAUSE
  • RESUME
  • RELOAD
  • EXIT

11 Security and Storage Considerations for Oracle Database Provider for DRDA

• Overview of Security and Storage for Oracle Database Provider for DRDA
• Authentication and Encryption in Oracle Database Provider for DRDA
  • Authentication Services
  • Encryption Services
• Database Roles in Oracle Database Provider for DRDA
  • DRDAAS_ADMIN_ROLE
  • DRDAAS_USER_ROLE
• Storage in Oracle Database Provider for DRDA
  • SYSIBM Tablespace
  • SYSIBM User

12 Restrictions on Using Oracle Database Provider for DRDA

• Resynch Manager
• Cursor HOLD Attribute Semantics
• DB2 Password Blank Padding
• Restrictions on Datatypes
  • DATE Datatype
  • Oracle Object-Relational Datatypes
  • TIMESTAMP Datatype
  • TIMESTAMP WITH TIMEZONE Datatype
  • XML Datatype
  • SYS.XMLType Datatype
• Extended Length Mode
• Other Restrictions

13 PL/SQL Packages Used by Oracle Database Provider for DRDA

• DBMS_DRDAAS_ADMIN Package
  • DBMS_DRDAAS_ADMIN Privilege Constants
  • GRANT_PRIVILEGE
  • REVOKE_PRIVILEGE
  • DROP_PACKAGE
  • DROP_PACKAGE_VN
  • DROP_PACKAGE_CT
  • SET_PROFILE
  • SET_LOCALDATE_FORMAT
  • SET_LOCALTIME_FORMAT
  • SET_TYPEMAP
• DBMS_DRDAAS Package
  • DBMS_DRDAAS Privilege Constants
  • BIND_PACKAGE
  • BIND_STATEMENT
  • END_BIND
  • GRANT_PRIVILEGE
  • REVOKE_PRIVILEGE
  • DROP_PACKAGE

14 SQL Statement Support in Oracle Database Provider for DRDA

• Overview of SQL Statement Support
• SQL Clause Restrictions
  • Internally Processed SQL Statements
  • Removed SQL Clauses that Retain Semantic Effect
  • Ignored SQL Clauses
  • Translated SQL Clauses
• Support for Special Registers
  • Retrieving Values from Special Registers
  • Setting Special Registers
  • Special Registers Supported by Oracle Database Provider for DRDA
    • APPLICATION ENCODING SCHEME
    • CLIENT_ACCTNG
    • CLIENT_APPLNAME
    • CLIENT_PROGRAMID
    • CLIENT_USERID
    • CLIENT_WRKSTNNAME
    • DATE
    • DBPARTITIONNUM
    • DEBUG MODE
    • DECFLOAT ROUNDING MODE
    • DEFAULT TRANSFORM GROUP
    • DEGREE
    • EXPLAIN MODE
    • EXPLAIN SNAPSHOT
    • FEDERATED ASYNCHRONY
    • IMPLICIT XMLPARSE OPTION
    • ISOLATION
    • LOCK TIMEOUT
    • LOCALE LC_TYPE
    • MAINTAINED TABLE TYPES FOR OPTIMIZATION
    • MEMBER
    • OPTIMIZATION HINT
    • PACKAGE PATH
    • PACKAGESET
    • PATH
    • PRECISION
    • QUERY ACCELERATION
    • QUERY OPTIMIZATION
    • REFRESH AGE
    • ROUTINE VERSION
    • RULES
    • SCHEMA
    • SERVER
    • SQL_CCFLAGS
    • SQLID
    • TIMESTAMP
    • USER
    • SESSION_USER
    • SYSTEM_USER
    • ENCRYPTION PASSWORD

A Scripts for Creating and Maintaining Oracle Database Provider for DRDA

• catdrdaas.sql
• catnodrdaas.sql
• drdapkg_db2.sql
• drdasqtt_translator_setup.sql
• drdasqt_set_profile_dd.sql

B Package Binding Options in Oracle Database Provider for DRDA

Glossary

Index