Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

SQL*Plus Prompts

Storage Measurements

1 Introduction

1.1 Introduction to the Oracle Transparent Gateway

1.2 Release 10g Gateways

1.3 Gateway Capabilities

1.3.1 Transparency at All Levels

1.3.2 Extended Database Services

1.3.3 Extended Advanced Networking, Internet and Intranet Support

1.3.4 Dynamic Dictionary Mapping

1.3.6 Data Definition Language

1.3.7 Data Control Language

1.3.8 Passthrough and Native DB2 SQL

1.3.9 Stored Procedures

1.3.10 Languages

1.3.11 Oracle Database Technology and Tools

1.3.12 SQL*Plus

1.3.13 Two-Phase Commit and Multi-Site Transactions

1.3.14 Site Autonomy

1.3.15 Migration and Coexistence

1.3.16 Security

1.5 Architecture

1.6 Implementation

1.7 How the Gateway Works

1.8 Oracle Tools and the Gateway

2 Release Information

2.1 Product Set

2.2 Changes and Enhancements

2.3 Product Migration

2.4 Bugs Fixed in Release 10.2.0

2.5 Known Problems

2.6 Known Restrictions

2.6.1 DB2 Considerations

2.6.2 SQL Limitations

3 System Requirements

3.1 Hardware Requirements

3.2 Software Requirements

3.3 Documentation Requirements

4 Installing the Gateway

4.1 Introduction

4.2 Before You Begin

4.3 Checklist for Gateway Installation

4.4 Installation Overview

4.5 Before Beginning Installation

4.6 Installing the Gateway from CD-ROM

4.7 Installation Complete

4.8 Removing the Gateway

5 Configuring the DRDA Server

5.1 Checklists for Configuring the DRDA Server

5.4 DB2/UDB (Universal Database)

6 Configuring SNAP-IX Interfaces

6.1 Checklist for Configuring the Communications Interfaces

6.2 Before You Begin

6.3 SNAP-IX Configuration Tool

6.4 Creating SNAP-IX Profiles for the Gateway

6.5 Independent Versus Dependent LUs

6.6 Creating SNA Definitions for the Gateway

6.6.1 Sample SNAP-IX Definitions

6.6.2 Configuring SNAP-IX

6.7 Using SNA Session Security Validation

6.8 SNA Conversation Security

6.8.1 SNA Security Option SECURITY=PROGRAM

6.8.2 SNA Security Option SECURITY=SAME

6.9 Testing the Connection

7 Configuring IBM Communication Server

7.1 Checklist for Configuring the Communications Interfaces

7.1.1 Step 1: Configuring Communication Server Profiles

7.1.2 Step 2: Creating Communication Server Profiles for the Gateway

7.1.2.1 Sample Profile Definitions

7.1.2.2 Profile Types

7.1.3 Step 3: Testing the Connection

7.2 Using SNA Session Security Validation

7.3 SNA Conversation Security

7.3.1 SNA Security Option SECURITY=PROGRAM

7.3.2 SNA Security Option SECURITY=SAME

8 Configuring SNAPlus2

8.1 Checklist for Configuring the Communications Interfaces

8.2 Before You Begin

8.3 SNAPlus2 Configuration Tool

8.4 Creating SNAPlus2 Profiles for the Gateway

8.5 Independent Versus Dependent LUs

8.6 Creating SNA Definitions for the Gateway

8.6.1 Sample SNAPlus2 Definitions

8.6.2 Configuring SNAPlus2

8.6.3 Invoking xsnapadmin

8.7 Using SNA Session Security Validation

8.7.1 SNA Conversation Security

8.8 Testing the Connection

9 Configuring TCP/IP

9.1 Before You Begin

9.2 Configuring TCP/IP under UNIX

10 Oracle Net

10.1 Checklists for Oracle Net

10.1.1 Configuring Oracle Net

10.1.2 Advanced Security Encryption

10.1.2.1 Setting Up Advanced Security Encryption for Test

10.1.2.2 Testing Advanced Security Encryptions

10.2 Oracle Net Introduction

10.3 Oracle Net Overview

10.4 Configuring Oracle Net

10.5 Advanced Security Encryption

10.6 Setting Up Advanced Security Encryption for Test

10.7 Testing Advanced Security Encryptions

11 Configuring the Gateway

11.1 Configuration Checklists

11.2 Choosing a Gateway System Identifier

11.2.1 Enter the SID on the Worksheet

11.3 Configurating the Gateway

11.4 Configuring the Host

11.4.1 Binding the DRDA Gateway Package

11.4.2 Binding Packages on DB2/Universal Database (DB2/UDB)

11.5 DRDA Gateway Package Considerations

11.5.1 Before Binding the DRDA Gateway Package

11.5.1.1 Step 1: Check all DRDA parameter settings

11.5.1.2 Step 2: If using DB2/UDB, then create ORACLE2PC table

11.5.2 Sample SQL scripts

11.5.2.1 Step 1: If server is DB2/OS390, DB2/400, or DB2/UDB, then run data dictionary scripts

11.6 Backup and Recovery of Gateway Configuration

11.7 Configuring the Oracle Integrating Server

11.7.1 Step 1: Create a database link

11.7.2 Step 2: Create synonyms and views

11.8 Accessing the Gateway from Other Oracle Databases

11.9 Accessing Other DRDA Servers

11.10 Gateway Installation and Configuration Complete

12 Using the Oracle Transparent Gateway for DRDA

12.1 Processing a Database Link

12.1.1 Creating Database Links

12.1.2 Dropping Database Links

12.1.3 Examining Available Database Links

12.1.4 Limiting the Number of Active Database Links

12.2 Accessing the Gateway

12.3 Accessing AS/400 File Members

12.4 Using the Synonym Feature

12.5 Performing Distributed Queries

12.5.1 Two-Phase Commit Processing

12.5.2 Distributed DRDA Transactions

12.6 Read-Only Gateway

12.7 Replicating in a Heterogeneous Environment

12.8 Copying Data from Oracle Database 10g to DRDA Server

12.9 Copying Data from DRDA Server to Oracle Database 10g Server

12.10 Tracing SQL Statements

13 Developing Applications

13.1 Gateway Appearance to Application Programs

13.1.1 Fetch Reblocking

13.2 Using Oracle Stored Procedures with the Gateway

13.3 Using DRDA Server Stored Procedures with the Gateway

13.3.1 Oracle Application and DRDA Server Stored Procedure Completion

13.3.2 Procedural Feature Considerations with DB2

13.4 Database Link Behavior

13.5 Oracle Database SQL Construct Processing

13.5.1 Compatible SQL Functions

13.5.2 Translated SQL Functions

13.5.3 Compensated SQL Functions

13.5.3.1 Post-Processing

13.5.4 Native Semantic SQL Functions

13.5.5 DB2/OS390 SQL Compatibility

13.5.6 DB2/Universal Database SQL Compatibility

13.5.7 DB2/400 SQL Compatibility

13.5.8 DB2/VM SQL Compatibility

13.6 Native Semantics

13.6.1 SQL Functions That Can Be Enabled

13.6.2 SQL Functions That Can Be Disabled

13.6.3 SQL Set Operators and Clauses

13.7 DRDA Data type to Oracle Data type Conversion

13.7.1 Performing Character String Operations

13.7.2 Converting Character String Data types

13.7.3 Performing Graphic String Operations

13.7.4 Performing Date and Time Operations

13.7.4.1 Processing TIME and TIMESTAMP Data

13.7.4.2 Processing DATE Data

13.7.4.3 Performing Date Arithmetic

13.7.6 HS_NLS_DATE_FORMAT Support

13.7.7 Oracle TO_DATE Function

13.7.8 Performing Numeric data type Operations

13.7.9 Mapping the COUNT Function

13.7.10 Performing Zoned Decimal Operations

13.8 Passing Native SQL Statements through the Gateway

13.8.1 Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

13.8.1.1 Examples

13.8.2 Retrieving Results Sets Through Passthrough

13.8.2.1 Example

13.9 Oracle Data Dictionary Emulation on a DRDA Server

13.9.1 Using the Gateway Data Dictionary

13.9.2 Using the DRDA Catalog

13.10 Defining the Number of DRDA Cursors

14 Security Considerations

14.1 Security Overview

14.2 Authenticating Application Logons

14.3 Defining and Controlling Database Links

14.3.1 Link Accessibility

14.3.2 Links and CONNECT Clauses

14.4 TCP/IP Security

14.5 Processing Inbound Connections

14.5.1 User ID Mapping

14.5.1.1 DB2/OS390

14.5.1.2 DB2/VM

14.5.1.3 DB2/400

14.5.1.4 DB2/Universal Database

14.6 Passwords in the Gateway Initialization File

15 Migration and Coexistence with Existing Gateways

15.1 Migrating Existing V4, V8, or V9 Gateway Instances to New Release

15.2 Backout Considerations When Migrating to New Releases

15.3 New and Changed Parameters When Migrating to Release 10g

15.3.1 New Parameters

15.3.2 Parameters That Have Been Changed in Usage

15.3.3 Parameters That Have Been Renamed

15.3.4 Obsolete Parameters

15.4 DRDA Server Considerations

15.5 Oracle Net Considerations

16 Error Messages, Diagnosis, and Reporting

16.1 Interpreting Gateway Error Messages

16.1.1 Errors Detected by the Oracle Integrating Server

16.1.2 Errors Detected by the Gateway

16.1.3 Errors Detected in the DRDA Software

16.1.4 Communication Errors

16.1.5 Errors Detected by the Server Database

16.2 Mapped Errors

16.3 Gateway Error Codes

16.4 SQL Tracing and the Gateway

16.4.1 SQL Tracing in the Oracle Database

16.4.2 SQL Tracing in the Gateway

A Oracle DB2 Data Dictionary Views

A.1 Supported Views

A.2 Data Dictionary View Tables

A.2.1 ALL_CATALOG

A.2.2 ALL_COL_COMMENTS

A.2.3 ALL_CONS_COLUMNS

A.2.4 ALL_CONSTRAINTS

A.2.5 ALL_INDEXES

A.2.6 ALL_IND_COLUMNS

A.2.7 ALL_OBJECTS

A.2.8 ALL_SYNONYMS

A.2.9 ALL_TABLES

A.2.10 ALL_TAB_COLUMNS

A.2.11 ALL_TAB_COMMENTS

A.2.12 ALL_USERS

A.2.13 ALL_VIEWS

A.2.14 COLUMN_PRIVILEGES

A.2.15 DICTIONARY

A.2.17 TABLE_PRIVILEGES

A.2.18 USER_CATALOG

A.2.19 USER_COL_COMMENTS

A.2.20 USER_CONSTRAINTS

A.2.21 USER_CONS_COLUMNS

A.2.22 USER_INDEXES

A.2.23 USER_OBJECTS

A.2.24 USER_SYNONYMS

A.2.25 USER_TABLES

A.2.26 USER_TAB_COLUMNS

A.2.27 USER_TAB_COMMENTS

A.2.28 USER_USERS

A.2.29 USER_VIEWS

B Sample Files

B.1 Sample Gateway Initialization File

B.2 Sample Oracle Net tnsnames.ora File

B.3 Sample Oracle Net listener.ora File

C DRDA-Specific Parameters

C.1 Modifying the Gateway Initialization File

C.2 Setting Parameters in the Gateway Initialization File

C.3 Syntax and Usage

C.4 Gateway Initialization File Parameters

C.4.1 DRDA_CACHE_TABLE_DESC

C.4.2 DRDA_CAPABILITY

C.4.3 DRDA_CODEPAGE_MAP

C.4.4 DRDA_COMM_BUFLEN

C.4.5 DRDA_CONNECT_PARM (SNA format)

C.4.6 DRDA_CONNECT_PARM (TCP/IP format)

C.4.7 DRDA_CMSRC_CM_IMMEDIATE

C.4.8 DRDA_DEFAULT_CCSID

C.4.9 DRDA_DESCRIBE_TABLE

C.4.10 DRDA_DISABLE_CALL

C.4.11 DRDA_FLUSH_CACHE

C.4.12 DRDA_GRAPHIC_CHAR_SIZE

C.4.13 DRDA_GRAPHIC_PAD_SIZE

C.4.14 DRDA_GRAPHIC_LIT_CHECK

C.4.15 DRDA_GRAPHIC_TO_MBCS

C.4.16 DRDA_ISOLATION_LEVEL

C.4.17 DRDA_LOCAL_NODE_NAME

C.4.18 DRDA_MBCS_TO_GRAPHIC

C.4.19 DRDA_OPTIMIZE_QUERY

C.4.20 DRDA_PACKAGE_COLLID

C.4.21 DRDA_PACKAGE_CONSTOKEN

C.4.22 DRDA_PACKAGE_NAME

C.4.23 DRDA_PACKAGE_OWNER

C.4.24 DRDA_PACKAGE_SECTIONS

C.4.25 DRDA_READ_ONLY

C.4.26 DRDA_RECOVERY_PASSWORD

C.4.27 DRDA_RECOVERY_USERID

C.4.28 DRDA_REMOTE_DB_NAME

C.4.29 DRDA_SECURITY_TYPE

C.4.30 FDS_CLASS

C.4.31 FDS_CLASS_VERSION

C.4.32 FDS_INSTANCE

C.4.33 HS_FDS_FETCH_ROWS

C.4.34 HS_LANGUAGE

C.4.35 HS_NLS_NCHAR

C.4.36 LOG_DESTINATION

C.4.37 ORA_MAX_DATE

C.4.38 ORA_NLS10

C.4.39 ORACLE_DRDA_TCTL

C.4.40 ORACLE_DRDA_TRACE

C.4.41 TRACE_LEVEL

D National Language Support

D.1 Overview of NLS Interactions

D.2 Client and Oracle Integrating Server Configuration

D.3 Gateway Language Interaction with DRDA Server

D.3.1 Gateway Configuration

D.3.2 NLS Parameters in the Gateway Initialization File

D.3.2.1 HS_LANGUAGE

D.3.2.2 HS_NLS_NCHAR

D.3.2.3 HS_NLS_DATE_FORMAT

D.3.2.4 HS_NLS_DATE_LANGUAGE

D.4 Gateway Codepage Map Facility

D.5 Multi-Byte and Double-Byte Support in the Gateway

D.6 Message Availability

D.7 Example of NLS Configuration

E Configuration Worksheet

F Quick Reference to Oracle SQL Functions

G Sample Applications

Index