Home
/
Middleware
/
Oracle Data Integrator
1/31
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
1
Introduction
1.1
Terminology
1.2
Using This Guide
Part I Databases, Files, and XML
2
Oracle Database
2.1
Introduction
2.1.1
Concepts
2.1.2
Knowledge Modules
2.2
Installation and Configuration
2.2.1
System Requirements and Certifications
2.2.2
Technology Specific Requirements
2.2.2.1
Using the SQL*Loader Utility
2.2.2.2
Using External Tables
2.2.2.3
Using Oracle Streams
2.2.3
Connectivity Requirements
2.3
Setting up the Topology
2.3.1
Creating an Oracle Data Server
2.3.1.1
Creation of the Data Server
2.3.2
Creating an Oracle Physical Schema
2.4
Setting Up an Integration Project
2.5
Creating and Reverse-Engineering an Oracle Model
2.5.1
Create an Oracle Model
2.5.2
Reverse-engineer an Oracle Model
2.6
Setting up Changed Data Capture
2.7
Setting up Data Quality
2.8
Designing a Mapping
2.8.1
Loading Data from and to Oracle
2.8.1.1
Loading Data from Oracle
2.8.1.2
Loading Data to Oracle
2.8.2
Integrating Data in Oracle
2.8.3
Designing an ETL-Style Mapping
2.9
Troubleshooting
2.9.1
Troubleshooting Oracle Database Errors
2.9.2
Common Problems and Solutions
3
Files
3.1
Introduction
3.1.1
Concepts
3.1.2
Knowledge Modules
3.2
Installation and Configuration
3.2.1
System Requirements and Certifications
3.2.2
Technology Specific Requirements
3.2.3
Connectivity Requirements
3.3
Setting up the Topology
3.3.1
Creating a File Data Server
3.3.1.1
Creation of the Data Server
3.3.2
Creating a File Physical Schema
3.4
Setting Up an Integration Project
3.5
Creating and Reverse-Engineering a File Model
3.5.1
Create a File Model
3.5.2
Reverse-engineer a File Model
3.5.2.1
Delimited Files Reverse-Engineering
3.5.2.2
Fixed Files Reverse-engineering using the Wizard
3.5.2.3
COBOL Copybook reverse-engineering
3.5.2.4
Customized Reverse-Engineering
3.6
Designing a Mapping
3.6.1
Loading Data From Files
3.6.2
Integrating Data in Files
3.6.2.1
IKM SQL to File Append
3.6.2.2
IKM File to File (Java)
4
Generic SQL
4.1
Introduction
4.1.1
Concepts
4.1.2
Knowledge Modules
4.2
Installation and Configuration
4.2.1
System Requirements and Certifications
4.2.2
Technology-Specific Requirements
4.2.3
Connectivity Requirements
4.3
Setting up the Topology
4.3.1
Creating a Data Server
4.3.2
Creating a Physical Schema
4.4
Setting up an Integration Project
4.5
Creating and Reverse-Engineering a Model
4.5.1
Create a Data Model
4.5.2
Reverse-engineer a Data Model
4.6
Setting up Changed Data Capture
4.7
Setting up Data Quality
4.8
Designing a Mapping
4.8.1
Loading Data From and to an ANSI SQL-92 Compliant Technology
4.8.1.1
Loading Data from an ANSI SQL-92 Compliant Technology
4.8.1.2
Loading Data to an ANSI SQL-92 Compliant Technology
4.8.2
Integrating Data in an ANSI SQL-92 Compliant Technology
4.8.3
Designing an ETL-Style Mapping
5
XML Files
5.1
Introduction
5.1.1
Concepts
5.1.2
Knowledge Modules
5.2
Installation and Configuration
5.2.1
System Requirements
5.2.2
Technologic Specific Requirements
5.2.3
Connectivity Requirements
5.3
Setting up the Topology
5.3.1
Creating an XML Data Server
5.3.1.1
Creation of the Data Server
5.3.2
Creating a Physical Schema for XML
5.4
Setting Up an Integration Project
5.5
Creating and Reverse-Engineering a XML File
5.5.1
Create an XML Model
5.5.2
Reverse-Engineering an XML Model
5.6
Designing a Mapping
5.6.1
Notes about XML Mappings
5.6.1.1
Targeting an XML Structure
5.6.1.2
Synchronizing XML File and Schema
5.6.1.3
Handling Large XML Files
5.6.2
Loading Data from and to XML
5.6.2.1
Loading Data from an XML Schema
5.6.2.2
Loading Data to an XML Schema
5.6.3
Integrating Data in XML
5.7
Troubleshooting
5.7.1
Detect the Errors Coming from XML
5.7.2
Common Errors
6
Complex Files
6.1
Introduction
6.1.1
Concepts
6.1.2
Knowledge Modules
6.2
Installation and Configuration
6.2.1
System Requirements
6.2.2
Technology Specific Requirements
6.2.3
Connectivity Requirements
6.3
Setting up the Topology
6.3.1
Creating a Complex File Data Server
6.3.1.1
Creation of the Data Server
6.3.2
Creating a Complex File Physical Schema
6.4
Setting Up an Integration Project
6.5
Creating and Reverse-Engineering a Complex File Model
6.5.1
Create a Complex File Model
6.5.2
Reverse-engineer a Complex File Model
6.6
Designing a Mapping
7
Microsoft SQL Server
7.1
Introduction
7.1.1
Concepts
7.1.2
Knowledge Modules
7.2
Installation and Configuration
7.2.1
System Requirements and Certifications
7.2.2
Technology Specific Requirements
7.2.2.1
Using the BULK INSERT Command
7.2.2.2
Using the BCP Command
7.2.2.3
Using Linked Servers
7.2.3
Connectivity Requirements
7.3
Setting up the Topology
7.3.1
Creating a Microsoft SQL Server Data Server
7.3.1.1
Creation of the Data Server
7.3.2
Creating a Microsoft SQL Server Physical Schema
7.4
Setting Up an Integration Project
7.5
Creating and Reverse-Engineering a Microsoft SQL Server Model
7.5.1
Create a Microsoft SQL Server Model
7.5.2
Reverse-engineer a Microsoft SQL Server Model
7.6
Setting up Changed Data Capture
7.7
Setting up Data Quality
7.8
Designing a Mapping
7.8.1
Loading Data from and to Microsoft SQL Server
7.8.1.1
Loading Data from Microsoft SQL Server
7.8.1.2
Loading Data to Microsoft SQL Server
7.8.2
Integrating Data in Microsoft SQL Server
8
Microsoft Excel
8.1
Introduction
8.1.1
Concepts
8.1.2
Knowledge Modules
8.2
Installation and Configuration
8.2.1
System Requirements and Certifications
8.2.2
Technology Specific Requirements
8.2.3
Connectivity Requirements
8.3
Setting up the Topology
8.3.1
Creating a Microsoft Excel Data Server
8.3.2
Creating a Microsoft Excel Physical Schema
8.4
Setting Up an Integration Project
8.5
Creating and Reverse-Engineering a Microsoft Excel Model
8.5.1
Create a Microsoft Excel Model
8.5.2
Reverse-engineer a Microsoft Excel Model
8.6
Designing a Mapping
8.6.1
Loading Data From and to Microsoft Excel
8.6.1.1
Loading Data from Microsoft Excel
8.6.1.2
Loading Data to Microsoft Excel
8.6.2
Integrating Data in Microsoft Excel
8.7
Troubleshooting
8.7.1
Decoding Error Messages
8.7.2
Common Problems and Solutions
9
Netezza
9.1
Introduction
9.1.1
Concepts
9.1.2
Knowledge Modules
9.2
Installation and Configuration
9.2.1
System Requirements and Certifications
9.2.2
Technology Specific Requirements
9.2.3
Connectivity Requirements
9.3
Setting up the Topology
9.3.1
Creating a Netezza Data Server
9.3.1.1
Creation of the Data Server
9.3.2
Creating a Netezza Physical Schema
9.4
Setting Up an Integration Project
9.5
Creating and Reverse-Engineering a Netezza Model
9.5.1
Create a Netezza Model
9.5.2
Reverse-engineer a Netezza Model
9.6
Setting up Data Quality
9.7
Designing a Mapping
9.7.1
Loading Data from and to Netezza
9.7.1.1
Loading Data from Netezza
9.7.1.2
Loading Data to Netezza
9.7.2
Integrating Data in Netezza
10
Teradata
10.1
Introduction
10.1.1
Concepts
10.1.2
Knowledge Modules
10.2
Installation and Configuration
10.2.1
System Requirements and Certifications
10.2.2
Technology Specific Requirements
10.2.3
Connectivity Requirements
10.3
Setting up the Topology
10.3.1
Creating a Teradata Data Server
10.3.1.1
Creation of the Data Server
10.3.2
Creating a Teradata Physical Schema
10.4
Setting Up an Integration Project
10.5
Creating and Reverse-Engineering a Teradata Model
10.5.1
Create a Teradata Model
10.5.2
Reverse-engineer a Teradata Model
10.6
Setting up Data Quality
10.7
Designing a Mapping
10.7.1
Loading Data from and to Teradata
10.7.1.1
Loading Data from Teradata
10.7.1.2
Loading Data to Teradata
10.7.2
Integrating Data in Teradata
10.7.3
Designing an ETL-Style Mapping
10.8
KM Optimizations for Teradata
10.8.1
Primary Indexes and Statistics
10.8.2
Support for Teradata Utilities
10.8.3
Support for Named Pipes
10.8.4
Optimized Management of Temporary Tables
11
IBM DB2 for iSeries
11.1
Introduction
11.1.1
Concepts
11.1.2
Knowledge Modules
11.2
Installation and Configuration
11.2.1
System Requirements and Certifications
11.2.2
Technology Specific Requirements
11.2.3
Connectivity Requirements
11.3
Setting up the Topology
11.3.1
Creating a DB2/400 Data Server
11.3.1.1
Creation of the Data Server
11.3.2
Creating a DB2/400 Physical Schema
11.4
Setting Up an Integration Project
11.5
Creating and Reverse-Engineering an IBM DB2/400 Model
11.5.1
Create an IBM DB2/400 Model
11.5.2
Reverse-engineer an IBM DB2/400 Model
11.6
Setting up Changed Data Capture
11.6.1
Setting up Trigger-Based CDC
11.6.2
Setting up Log-Based CDC
11.6.2.1
How does it work?
11.6.2.2
CDCRTVJRN Program Details
11.6.2.3
Installing the CDC Components on iSeries
11.6.2.4
Using the CDC with the Native Journals
11.6.2.5
Problems While Reading Journals
11.7
Setting up Data Quality
11.8
Designing a Mapping
11.8.1
Loading Data from and to IBM DB2 for iSeries
11.8.1.1
Loading Data from IBM DB2 for iSeries
11.8.1.2
Loading Data to IBM DB2 for iSeries
11.8.2
Integrating Data in IBM DB2 for iSeries
11.9
Specific Considerations with DB2 for iSeries
11.9.1
Installing the Run-Time Agent on iSeries
11.9.2
Alternative Connectivity Methods for iSeries
11.9.2.1
Using Client Access
11.9.2.2
Using the IBM JT/400 and Native Drivers
11.10
Troubleshooting
11.10.1
Troubleshooting Error messages
11.10.2
Common Problems and Solutions
11.10.2.1
Connection Errors
12
IBM DB2 UDB
12.1
Introduction
12.2
Concepts
12.3
Knowledge Modules
12.4
Specific Requirements
Part II Business Intelligence
13
Oracle Business Intelligence Enterprise Edition
13.1
Introduction
13.1.1
Concepts
13.1.2
Knowledge Modules
13.2
Installation and Configuration
13.2.1
System Requirements and Certifications
13.2.2
Technology Specific Requirements
13.2.3
Connectivity Requirements
13.3
Setting up the Topology
13.3.1
Creating an Oracle BI Data Server
13.3.1.1
Creation of the Data Server
13.3.2
Creating an Oracle BI Physical Schema
13.4
Setting Up an Integration Project
13.5
Creating and Reverse-Engineering an Oracle BI Model
13.5.1
Create an Oracle BI Model
13.5.2
Reverse-engineer an Oracle BI Model
13.6
Setting up Data Quality
13.7
Designing a Mapping
13.7.1
Loading Data from and to Oracle BI
13.7.1.1
Loading Data from Oracle BI
13.7.1.2
Loading Data to Oracle BI
13.7.2
Integrating Data in Oracle BI
14
Oracle Business Intelligence Enterprise Edition Data Lineage
14.1
Introduction
14.1.1
Components
14.1.2
Lineage Lifecycle
14.1.2.1
Setting up the Lineage
14.1.2.2
Refreshing the Lineage
14.1.2.3
Using the Lineage
14.2
Installing the Lineage in an OBIEE Server
14.2.1
Installation Overview
14.2.2
Requirements
14.2.3
Installation Instructions
14.2.3.1
Installing and Starting the OBIEE Lineage Wizard
14.2.3.2
Deploying the OBIEE Lineage Artifacts using the Wizard
14.2.4
Post-Installation Tasks
14.3
Exporting Metadata from OBIEE and Refreshing the OBIEE Lineage
14.4
Refreshing the OBIEE Lineage from Existing Exports
14.4.1
Exporting the OBIEE Repository Documentation to a Text File
14.4.2
Exporting the OBIEE Web Catalog Report to a Text File
14.4.3
Refreshing the OBIEE Lineage From Existing Exports
14.5
Automating the Lineage Tasks
14.5.1
Configuring the Scripts
14.5.2
Automating Lineage Deployment
14.5.3
Automating Lineage Refresh
14.6
Using the Lineage in OBIEE Dashboards
14.6.1
Viewing Execution Statistics
14.6.2
Viewing and Filtering Lineage Data
14.6.3
Using the Dashboard
14.6.4
Using Lineage and Hierarchy
14.6.5
Using Contextual Lineage
Part III Other Technologies
15
JMS
15.1
Introduction
15.1.1
Concepts
15.1.1.1
JMS Message Structure
15.1.1.2
Using a JMS Destination
15.1.2
Knowledge Modules
15.2
Installation and Configuration
15.2.1
System Requirements and Certifications
15.2.2
Technology Specific Requirements
15.2.3
Connectivity Requirements
15.3
Setting up the Topology
15.3.1
Creating a JMS Data Server
15.3.1.1
Creation of the Data Server
15.3.2
Creating a JMS Physical Schema
15.4
Setting Up an Integration Project
15.5
Creating and Defining a JMS Model
15.5.1
Create a JMS Model
15.5.2
Defining the JMS Datastores
15.6
Designing a Mapping
15.6.1
Loading Data from a JMS Source
15.6.2
Integrating Data in a JMS Target
15.7
JMS Standard Properties
15.7.1
Using JMS Properties
15.7.1.1
Declaring JMS Properties
15.7.1.2
Filtering on the Router
15.7.1.3
Filtering on the Client
15.7.1.4
Using Property Values as Source Data
15.7.1.5
Setting Properties when Sending a Message
16
JMS XML
16.1
Introduction
16.1.1
Concepts
16.1.1.1
JMS Message Structure
16.1.1.2
Using a JMS Destination
16.1.2
Knowledge Modules
16.2
Installation and Configuration
16.2.1
System Requirements and Certifications
16.2.2
Technology Specific Requirements
16.2.3
Connectivity Requirements
16.3
Setting up the Topology
16.3.1
Creating a JMS XML Data Server
16.3.1.1
Creation of the Data Server
16.3.2
Creating a JMS XML Physical Schema
16.4
Setting Up an Integration Project
16.5
Creating and Reverse-Engineering a JMS XML Model
16.5.1
Create a JMS XML Model
16.5.2
Reverse-Engineering a JMS XML Model
16.6
Designing a Mapping
16.6.1
Loading Data from a JMS XML Source
16.6.2
Integrating Data in a JMS XML Target
17
LDAP Directories
17.1
Introduction
17.1.1
Concepts
17.1.2
Knowledge Modules
17.2
Installation and Configuration
17.2.1
System Requirements
17.2.2
Technologic Specific Requirements
17.2.3
Connectivity Requirements
17.3
Setting up the Topology
17.3.1
Creating an LDAP Data Server
17.3.1.1
Creation of the Data Server
17.3.2
Creating a Physical Schema for LDAP
17.4
Setting Up an Integration Project
17.5
Creating and Reverse-Engineering an LDAP Directory
17.5.1
Create an LDAP Model
17.5.2
Reverse-Engineering an LDAP Model
17.6
Designing a Mapping
17.6.1
Loading Data from and to LDAP
17.6.1.1
Loading Data from an LDAP Directory
17.6.1.2
Loading Data to an LDAP Directory
17.6.2
Integrating Data in an LDAP Directory
17.7
Troubleshooting
18
Oracle TimesTen In-Memory Database
18.1
Introduction
18.1.1
Concepts
18.1.2
Knowledge Modules
18.2
Installation and Configuration
18.2.1
System Requirements and Certifications
18.2.2
Technology Specific Requirements
18.2.3
Connectivity Requirements
18.3
Setting up the Topology
18.3.1
Creating a TimesTen Data Server
18.3.1.1
Creation of the Data Server
18.3.2
Creating a TimesTen Physical Schema
18.4
Setting Up an Integration Project
18.5
Creating and Reverse-Engineering a TimesTen Model
18.5.1
Create a TimesTen Model
18.5.2
Reverse-engineer a TimesTen Model
18.6
Setting up Data Quality
18.7
Designing a Mapping
18.7.1
Loading Data from and to TimesTen
18.7.1.1
Loading Data from TimesTen
18.7.1.2
Loading Data to TimesTen
18.7.2
Integrating Data in TimesTen
19
Oracle Changed Data Capture Adapters
19.1
Introduction
19.1.1
Concepts
19.1.2
Knowledge Modules
19.2
Installation and Configuration
19.2.1
System Requirements
19.2.2
Technology Specific Requirements
19.2.3
Connectivity Requirements
19.3
Setting up the Topology
19.3.1
Creating an Attunity Stream Data Server
19.3.1.1
Creation of the Data Server
19.3.2
Creating an Attunity Stream Physical Schema
19.4
Setting Up an Integration Project
19.5
Creating and Reverse-Engineering an Attunity Stream Model
19.5.1
Create an Attunity Stream Model
19.5.2
Reverse-engineer an Attunity Stream Model
19.6
Designing a Mapping Using the LKM Attunity to SQL
20
Oracle GoldenGate
20.1
Introduction
20.1.1
Overview of the GoldeGate CDC Process
20.1.2
Knowledge Modules
20.2
Installation and Configuration
20.2.1
System Requirements and Certifications
20.2.2
Technology Specific Requirements
20.2.3
Connectivity Requirements
20.3
Working with the Oracle GoldenGate JKMs
20.3.1
Define the Topology
20.3.1.1
Define the Source Data Server
20.3.1.2
Create the Source Physical Schema
20.3.1.3
Define the Staging Server
20.3.1.4
Create the Staging Physical Schema
20.3.1.5
Define the Oracle GoldenGate Data Servers
20.3.1.6
Create the Oracle GoldenGate Physical Schemas
20.3.1.7
Create the Oracle GoldenGate Logical Schemas
20.3.2
Create the Replicated Tables
20.3.3
Set Up an Integration Project
20.3.4
Configure CDC for the Source Datastores
20.3.4.1
Create Oracle GoldenGate Physical Schemas from the model
20.3.5
Configure and Start Oracle GoldenGate Processes (Offline mode only)
20.3.6
Design Mappings Using Replicated Data
20.4
Advanced Configuration
20.4.1
Initial Load Method
20.4.2
Tuning Replication Performances
20.4.3
One Source Multiple Staging Configuration (Offline mode only)
21
Oracle SOA Suite Cross References
21.1
Introduction
21.1.1
Concepts
21.1.1.1
General Principles
21.1.1.2
Cross Reference Table Structures
21.1.1.3
Handling Cross Reference Table Structures
21.1.2
Knowledge Modules
21.1.3
Overview of the SOA XREF KM Process
21.1.3.1
Loading Phase (LKM)
21.1.3.2
Integration and Cross-Referencing Phase (IKM)
21.1.3.3
Updating/Deleting Processed Records (LKM)
21.2
Installation and Configuration
21.2.1
System Requirements and Certifications
21.2.2
Technology Specific Requirements
21.2.3
Connectivity Requirements
21.3
Working with XREF using the SOA Cross References KMs
21.3.1
Defining the Topology
21.3.2
Setting up the Project
21.3.3
Designing a Mapping with the Cross-References KMs
21.4
Knowledge Module Options Reference
Part IV Appendices
A
Oracle Data Integrator Driver for LDAP Reference
A.1
Introduction to Oracle Data Integrator Driver for LDAP
A.2
LDAP Processing Overview
A.2.1
LDAP to Relational Mapping
A.2.1.1
General Principle
A.2.1.2
Grouping Factor
A.2.1.3
Mapping Exceptions
A.2.1.4
Reference LDAP Tree
A.2.2
Managing Relational Schemas
A.2.2.1
Relational Schema Storage
A.2.2.2
Accessing Data in the Relational Structure
A.3
Installation and Configuration
A.3.1
Driver Configuration
A.3.2
Using an External Database to Store the Data
A.3.2.1
Passing the Properties in the Driver URL
A.3.2.2
Setting the Properties in ODI Studio
A.3.2.3
Setting the Properties in a Properties File
A.3.3
LDAP Directory Connection Configuration
A.3.4
Table Aliases Configuration
A.4
SQL Syntax
A.4.1
SQL Statements
A.4.1.1
DISCONNECT
A.4.1.2
INSERT INTO
A.4.1.3
SELECT
A.4.1.4
UPDATE
A.4.1.5
Expressions, Condition & values
A.4.2
SQL FUNCTIONS
A.5
JDBC API Implemented Features
B
Oracle Data Integrator Driver for XML Reference
B.1
Introduction to Oracle Data Integrator Driver for XML
B.2
XML Processing Overview
B.2.1
XML to SQL Mapping
B.2.2
XML Namespaces
B.2.3
Managing Schemas
B.2.3.1
Schema Storage
B.2.3.2
Multiple Schemas
B.2.3.3
Accessing Data in the Schemas
B.2.3.4
Case Sensitivity
B.2.3.5
Loading/Synchronizing
B.2.4
Locking
B.2.5
XML Schema (XSD) Support
B.3
Installation and Configuration
B.3.1
Driver Configuration
B.3.2
Automatically Create Multiple Schemas
B.3.3
Using an External Database to Store the Data
B.4
Detailed Driver Commands
B.4.1
CREATE FILE
B.4.2
CREATE FOREIGNKEYS
B.4.3
CREATE XMLFILE
B.4.4
CREATE SCHEMA
B.4.5
DROP FOREIGNKEYS
B.4.6
DROP SCHEMA
B.4.7
LOAD FILE
B.4.8
SET SCHEMA
B.4.9
SYNCHRONIZE
B.4.10
UNLOCK FILE
B.4.11
TRUNCATE SCHEMA
B.4.12
VALIDATE
B.4.13
WRITE MAPPING FILE
B.5
SQL Syntax
B.5.1
SQL Statements
B.5.1.1
COMMIT
B.5.1.2
CREATE TABLE
B.5.1.3
DELETE
B.5.1.4
DISCONNECT
B.5.1.5
DROP TABLE
B.5.1.6
INSERT INTO
B.5.1.7
ROLLBACK
B.5.1.8
SELECT
B.5.1.9
SET AUTOCOMMIT
B.5.1.10
UPDATE
B.5.1.11
Expressions, Condition and Values
B.5.2
SQL FUNCTIONS
B.6
JDBC API Implemented Features
B.7
XML Schema Supported Features
B.7.1
Datatypes
B.7.2
Supported Elements
B.7.2.1
All
B.7.2.2
Attribute
B.7.2.3
AttributeGroup
B.7.2.4
Choice
B.7.2.5
ComplexContent
B.7.2.6
ComplexType
B.7.2.7
Element
B.7.2.8
Extension
B.7.2.9
Group
B.7.2.10
Import
B.7.2.11
Include
B.7.2.12
List
B.7.2.13
Restriction
B.7.2.14
Schema
B.7.2.15
Sequence
B.7.2.16
SimpleContent
B.7.2.17
SimpleType
B.7.3
Unsupported Features
B.7.3.1
Unsupported Elements
B.7.3.2
Unsupported Features
B.7.3.3
Unsupported Datatypes
C
Oracle Data Integrator Driver for Complex Files Reference
C.1
Introduction to Oracle Data Integrator Driver for Complex Files
C.2
Complex Files Processing Overview
C.2.1
Generating the Native Schema
C.2.2
XML to SQL Mapping
C.2.3
Supported Features
C.3
Driver Configuration
C.4
Detailed Driver Commands
C.5
JDBC API and XML Schema Supported Features
Scripting on this page enhances content navigation, but does not change the content in any way.