Go to main content
1/23
Contents
List of Tables
Title and Copyright Information
Preface
Intended Audience
Product Name
Documentation Accessibility
The Oracle Documentation Set
Related Documents
SQL*Plus Prompts
Storage Measurements
Conventions Used in this Guide
Switches
1
Introduction
1.1
Version 10 Gateways
1.1.1
Advantages of the Gateway
1.1.1.1
Transparency at All Levels
1.1.1.2
Extension of Database Services
1.1.1.3
Extension of Advanced Networking, Internet, and Intranet Support
1.1.1.4
Dynamic Dictionary Mapping
1.1.2
SQL
1.1.2.1
Data Definition Language
1.1.2.2
Data Control Language
1.1.2.3
Passthrough and Native DB2 SQL
1.1.3
Stored Procedures
1.1.3.1
Oracle Stored Procedures
1.1.3.2
Native DB2 Stored Procedures
1.1.3.3
Native DB2 User Defined Functions
1.1.4
Applications
1.1.4.1
Oracle Developer
1.1.4.2
Oracle Discoverer
1.1.4.3
SQL*Plus
1.1.4.4
Oracle Database Server Technology and Tools
1.2
Two-Phase Commit and Multisite Transactions
1.3
Site Autonomy
1.4
Migration and Coexistence
1.5
Security
1.6
Protection of Current Investment
1.7
Gateway Architecture
1.8
How the Gateway Works
2
Release Information
2.1
Product Set
2.2
Changes and Enhancements
2.2.1
Changes and Enhancements in Release 8.1.7
2.2.2
Changes and Enhancements in Release 9.2.0
2.2.3
Changes and Enhancements in Release 10.1.0.2.0
2.2.4
Changes and Enhancements in Release 10.2.0.2.0
2.3
Known Problems
2.4
Known Restrictions
3
System Requirements
3.1
Resource Requirements
3.1.1
CPU
3.1.2
Disk Space
3.1.3
Virtual Memory
3.2
Software Requirements
3.2.1
Operating System
3.2.2
UNIX System Services (USS)
3.2.3
IBM Maintenance
3.2.4
DB2 Maintenance
3.3
Oracle Database Server Requirements
3.4
Oracle Net Requirements
3.4.1
Oracle Net IBM TCP/IP
3.5
Distribution Kit
4
Installation
4.1
Installation Checklists
4.1.1
Installation Checklist
4.1.2
Post-installation Checklist
4.2
Product Installation
4.2.1
Step 1: Perform Preinstallation Tasks
4.2.2
Step 2: Install the Oracle Transparent Gateway for DB2 Software
4.3
Postinstallation Steps
4.3.1
Step 1: Download and Install Patches
4.3.2
Step 2: Add Program Properties
4.3.3
Step 3: Authorize the Gateway Load Library
4.3.4
Step 4: Re-IPL z/OS or Use Dynamic z/OS Commands
4.4
Summary
5
Configuring a Gateway Service
5.1
Overview
5.2
Gateway Service Definition
5.2.1
Service Name
5.2.2
TYPE
5.2.3
PROC
5.2.4
PARM
5.2.5
MAXAS
5.2.6
JOBNAME
5.2.7
SID
5.3
Gateway Region JCL
5.3.1
ORA$ENV
5.3.2
ORA$FPS
5.3.3
ORA$LIB
5.3.4
SQLNET
5.3.5
STEPLIB
5.3.6
SYSPRINT
5.3.7
Sample Gateway Region JCL Procedure
5.4
Gateway Region Parameters
5.4.1
ALERT_DSNAME | ADSN
5.4.2
ALERT_MAX | AMAX
5.4.3
ALERT_MIN | AMIN
5.4.4
DSN_PREFIX_DB | ORAPREFD
5.4.5
DEDICATED_TCB
5.4.6
IDLE_TIMEOUT | ITIMEOUT
5.4.7
INIT_ADR_SPACES | INTADSPC
5.4.8
INIT_STACK_SIZE | INTSTKSZ
5.4.9
LOGON_AUTH | LGNAUTH
5.4.10
MAX_SESSION_MEM | MAXSMEM
5.4.11
MAX_SESSIONS | MAXSESS
5.4.12
PRIMARY_ASC_MODE | PRIMASCM
5.4.13
REGION_MEM_RESERVE | REGMRES
5.4.14
REGION_MEM_RESERVE (region_memory)
5.4.15
SERVER_LOADMOD | SRVRLMOD
5.4.16
SMF_STAT_RECNO | SMFSTRCN
5.4.17
TRACE_DSNAME | TDSN
5.4.18
G4DB2ENV (Environment Variables and HS Initialization parameters)
5.4.19
Parameters for Environment Variables in Member G4DB2ENV
5.4.20
CURRDEGREE
5.4.21
DB2DESCTAB
5.4.22
DB2LONGMSG
5.4.23
DB2WARNING
5.4.24
DB2STATS
5.4.25
DB2READONLY
5.4.26
FDS_CLASS_VERSION
5.4.27
FLUSH_CACHE_ON_COMMIT
5.4.28
DB2CAP
5.4.29
TARGET
5.4.30
DB2SSN
5.4.31
DB2PLAN
5.4.32
ORARECID
5.4.33
ORA_MAX_DATE
5.4.34
ORACLE_TIMESTAMP = YES| NO (default)
5.4.35
TRACELEVEL
5.4.36
TO_NUMBER_OFF = YES | NO (default)
5.4.37
CNV_LIT_FMT = YES | NO (default)
5.4.38
EMPTYSTR_TO_NULL_OFF = YES | NO (default)
5.4.39
EMPTYSTR_TO_NULL_WHERE_OFF = YES | NO (default)
5.4.40
LIKE_OFF = YES | NO (default)
5.4.41
NVL_TO_VALUE_OFF = YES | NO (default)
5.4.42
SELECT_CONCAT_ON = YES | NO (default)
5.4.43
HS Initialization Parameters for Member G4DB2ENV
5.5
Checklists
5.5.1
Configuration Checklist
5.5.2
Post Configuration Checklist
5.6
Creating a Gateway Instance
5.6.1
Step 1: Run the Configuration Utility
5.6.2
Step 2: Customize JCL Procedures and Parameter Files
5.6.2.1
JCL Procedures
5.6.2.2
Parameter Files
5.6.3
Step 3: Copy the Subsystem PROCs to a System PROCLIB
5.6.4
Step 4: Make Authorization and Local Date Exits Available to DB2
5.6.5
Step 5: Run the Scripts to Create Required Tables and Views in DB2
5.6.6
Step 6: Bind the DB2 Package
5.6.7
Step 7: Bind the DB2 Plan
5.6.8
Step 8: Grant EXECUTE on DB2 Plan
5.6.9
Step 9: Edit the PARMLIB Members
5.6.9.1
DATACLAS (classname)
5.6.9.2
DEFAULT_SPACE (primary secondary)
5.6.9.3
FILE_GROUP (name)
5.6.9.4
MGMTCLAS (classname)
5.6.9.5
STORCLAS (classname)
5.6.9.6
UNIT (unitname)
5.6.9.7
VOLUMES (volser)
5.6.9.8
SQLNETLG
5.6.10
Step 10: Associate User IDs with Services
5.6.11
Step 11: Define and Start OSDI Services
5.6.12
Step 12: Start the Gateway
5.7
Post-configuration Steps
5.7.1
Step 1: Move Reentrant Modules to z/OS Link Pack Areas
5.7.2
Step 2: Examine Oracle Dump Data Sets and Modify as Necessary
5.7.3
Step 3: Examine Oracle Trace Data Sets and Modify as Necessary
6
Oracle Net
6.1
Overview
6.2
OSDI Listener Architecture
6.3
OSDI Listener File Names
6.4
Configuring the OSDI Listener
6.4.1
Network Service Definition
6.4.2
Service Name
6.4.3
TYPE
6.4.4
PROC
6.4.5
PARM
6.4.5.1
Example of Network Service Definition
6.4.6
OSDI Listener Region JCL
6.4.6.1
Example of Network Service Procedure JCL
6.4.6.2
Example of NET8LOG output
6.4.7
TCP/IP Network Considerations
6.4.8
Client-Server Access Using the OSDI Listener
6.4.8.1
Remote Clients
6.4.8.2
Name Server
6.4.8.3
LDAP Server
6.5
Operating the OSDI Listener
6.6
Formatting OSDI Listener Trace Files
6.7
Oracle Advanced Security Option Encryption
6.7.1
Setting Up ASO Encryption for Test
6.7.1.1
Checklist for Setting Up ASO Encryption
6.7.1.2
Step 1: Set ASO Encryption Parameters for the Server
6.7.1.3
Step 2: Set ASO Encryption Parameters for the Client
6.7.2
Testing ASO Encryption
6.7.2.1
Checklist for Testing ASO Encryption
6.7.2.2
Step 1: Connect Client and Server
6.7.2.3
Step 2: Reset Configuration Parameters on Server
7
Administering the Gateway
7.1
Operation of the Gateway Subsystem with OSDI
7.2
Controlling Access to OSDI Subsystem Commands
7.3
Controlling Access to OSDI Services
7.4
Gateway Security
7.5
SAF Router Considerations
7.6
Gateway User Exit Facility
7.6.1
Specifying an Exit Module
7.7
Sample Exit Programs
8
Using the Gateway
8.1
Database Link Behavior
8.1.1
Creating Database Links
8.1.2
Creating Database Links Using Oracle Net
8.1.3
Guidelines for Database Links
8.1.4
Accessing Data through Database Links
8.1.5
Dropping Database Links
8.1.6
Examining Available Database Links
8.1.7
Limiting the Number of Active Database Links
8.2
Managing Threads
8.2.1
KEEPALIVE
8.2.2
Canceling DB2 Threads
8.3
Gateway CPU Time
8.4
Using DB2 Cursors
8.5
Using the Synonym Feature
8.6
Read-Only Gateway
8.7
Performing Distributed Queries
8.7.1
Example of a Distributed Query
8.7.2
Two-Phase Commit Processing
8.7.3
Distributed DB2 Transactions
8.8
Replicating in a Heterogeneous Environment
8.8.1
Oracle Database Server Triggers
8.8.2
Oracle Materialized View
8.9
Copying Data from the Oracle Database Server to the DB2 Server
8.9.1
Triggers
8.9.2
SQL*Plus COPY Command
8.9.3
STREAMS Replication
8.10
Copying Data to the Oracle Database Server from the DB2 Server
9
Developing Applications
9.1
Gateway Appearance to Application Programs
9.2
Array Processing
9.2.1
Fetch Reblocking
9.3
Using Oracle Stored Procedures with the Gateway
9.4
Using DB2 Stored Procedures with the Gateway
9.4.1
Oracle Application DB2 Stored Procedure Execution
9.4.2
Procedural Feature Considerations with DB2
9.5
Passing DB2 SQL Statements Through the Gateway
9.5.1
Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function
9.5.1.1
Examples
9.5.2
Retrieving Result Sets Through Passthrough
9.5.2.1
Example
9.6
DB2 Data Types to Oracle Data Type Conversion
9.6.1
Performing Character String Operations
9.6.2
Converting Character String Data Types
9.6.3
Performing Date and Time Operations
9.6.4
DB2 Local Date Exit
9.6.5
Date Considerations in SQL Coding
9.6.6
NLS_DATE_FORMAT Support
9.6.7
Oracle TO_DATE Function
9.6.8
Date Arithmetic
9.6.9
Performing Numeric Data Type Operations
9.6.10
Oracle ROWID Column
9.6.11
Double Byte Character Set Support
9.6.12
CHAR FOR BIT DATA
9.7
SQL Functions
9.8
Oracle Database Server SQL Construct Processing
9.8.1
SELECT Without the FOR UPDATE Clause
9.8.2
SELECT FOR UPDATE, INSERT,UPDATE, and DELETE Clauses
9.9
Oracle Database Server and DB2 Differences
9.9.1
Mass Delete from a Segmented Tablespace
9.9.2
Oracle Bind Variables
9.10
Oracle Data Dictionary Emulation on a DB2 Server
9.10.1
Using the Gateway Data Dictionary
9.10.2
DB2 Special Registers
10
Error Messages, Diagnosis, and Reporting
10.1
Message and Error Code Processing
10.1.1
Mapping DB2 Error Messages to Oracle Error Messages
10.1.2
Interpreting Message Formats
10.1.3
Messages Generated by Oracle Transparent Gateway for DB2
10.1.4
Examples
10.1.5
Diagnosing Errors Detected by the Oracle Database Server
10.2
Oracle Support Services
10.3
Providing Error Documentation
10.4
General Documentation Requirements
10.5
Error Diagnosis
10.5.1
Components
10.6
Error Categories
10.6.1
Documentation Errors
10.6.2
Incorrect Output
10.6.3
Oracle Database Server External Error
10.6.4
Abend
10.6.5
Program Loop
10.6.6
Performance
10.6.7
Missing Functionality
10.7
System Dumps
10.7.1
System Dump Data Sets
10.7.2
Operator-Initiated Dumps
10.8
GTF
11
Migration and Coexistence with Existing Gateways
11.1
OSDI Differences
11.1.1
Summary of Changes
11.1.1.1
OSDI Subsystems
11.1.1.2
Gateway Service
11.1.1.3
Oracle Net or Network Service
11.1.1.4
Commands and Messages
11.1.1.5
Error Diagnosis and Reporting
11.1.2
Configuring and Initializing an OSDI Subsystem
11.1.3
Configuring a Gateway Service
11.1.3.1
SID
11.1.3.2
Gateway Instance JCL
11.1.3.3
Oracle Net Access
11.1.4
File Processing Considerations
11.1.5
Operating a Gateway Service
11.1.6
Oracle Net or Network Service
11.1.6.1
Configuring Network Service
11.1.6.2
Operating Network Service
11.1.6.3
Computer Associates or Interlink SNS/TCPaccess Support
11.1.6.4
IXCF Support
11.1.6.5
Using Network Service
11.2
Migration and Upgrade
11.2.1
Release Incompatibilities
11.2.1.1
Local Database Links
11.2.2
Migration and Upgrade Steps
11.2.2.1
Step 1: Create and Configure an OSDI Subsystem
11.2.2.2
Step 2: Create an OSDI Gateway Service
11.2.2.3
Step 3: Create and Configure OSDI Net Service
11.2.2.4
Step 4: Establish Security
11.2.2.5
Step 5: Ensure User Exits Are Available to DB2
11.2.2.6
Step 6: Prepare the DB2 Environment
11.2.2.7
Step 7: Start the OSDI Services
11.2.2.8
Step 8: Test the Gateway
11.2.3
Configuring Multiple OSDI Gateway Services
11.2.4
MPM/TNS and OSDI Coexistence
11.2.5
Release 10
g
Coexistence with Prior Releases of the Gateway
12
Globalization Support
12.1
Overview
12.2
Obsolete NLS Parameters
12.3
DB2 Character Sets Handled Automatically
12.4
Oracle Database 10
g
for z/OS
12.4.1
Double-Byte Character Support
12.5
Oracle Database Server and Client Configuration
12.6
Message Availability
A
OSDI Subsystem Command Reference
A.1
OSDI Command Reference
A.2
Command Types and Processing
A.3
System Symbols in Commands
A.4
Definition Commands
A.5
Structures
A.6
Service Group Definition Commands
A.6.1
DEFINE
A.6.2
Define Parameters
A.6.3
ALTER
A.6.4
Alter Parameters
A.6.5
SHOW
A.6.5.1
Show Parameters
A.7
Service Definition Commands
A.7.1
DEFINE
A.7.2
Define Parameters
A.7.3
ALTER
A.7.4
Alter Parameters
A.7.5
SHOW
A.7.6
Show Parameters
A.8
Operating Commands
A.9
Available Commands
A.10
Commands
A.10.1
START
A.10.2
DISPLAY
A.10.3
DRAIN
A.10.4
RESUME
A.10.5
RESUME Parameters
A.10.6
STOP
A.10.7
STOP Parameters
A.11
OSDI Command Keyword Abbreviations
B
The Oracle SMF Interface
B.1
Activating SMF Records
B.1.1
Specifying the Oracle Gateway Record Type
B.1.1.1
Using the OSDI SMF_STAT_RECNO Parameter
B.1.2
Starting SMF Recording
B.1.3
Stopping SMF Recording
B.2
Events Generating SMF Records
B.3
Interpreting an Oracle SMF Record
B.3.1
Contents of the SMF Header Section
B.3.2
Contents of the SMF Correlation Section
B.3.3
Contents of the SMF OSDI Data Section
B.3.4
Contents of the SMF Database Engine Data Section
B.3.5
Contents of the SMF Oracle Net Data Section
B.4
ORAFMTO Sample Formatting Program
C
Data Dictionary Views
C.1
ALL_CATALOG
C.2
ALL_COL_COMMENTS
C.3
ALL_CON_COLUMNS
C.4
ALL_CONSTRAINTS
C.5
ALL_IND_COLUMNS
C.6
ALL_INDEXES
C.7
ALL_OBJECTS
C.8
ALL_SYNONYMS
C.9
ALL_TAB_COLUMNS
C.10
ALL_TAB_COMMENTS
C.11
ALL_TABLES
C.12
ALL_USERS
C.13
ALL_VIEWS
C.14
COLUMN_PRIVILEGES
C.15
OTGDB2.OTGREGISTER
C.16
TABLE_PRIVILEGES
C.17
USER_CATALOG
C.18
USER_COL_COMMENTS
C.19
USER_CONS_COLUMNS
C.20
USER_CONSTRAINTS
C.21
USER_INDEXES
C.22
USER_OBJECTS
C.23
USER_SYNONYMS
C.24
USER_TAB_COLUMNS
C.25
USER_TAB_COMMENTS
C.26
USER_TABLES
C.27
USER_USERS
C.28
USER_VIEWS
D
Quick Reference to Oracle SQL Functions
E
Sample Applications
E.1
DB2IND
E.2
ORAIND
F
Installation Reference
F.1
Choosing Data Set Name Qualifiers
Index
Scripting on this page enhances content navigation, but does not change the content in any way.