第1页 / 共382页
第2页 / 共382页
第3页 / 共382页
第4页 / 共382页
第5页 / 共382页
第6页 / 共382页
第7页 / 共382页
第8页 / 共382页
odi 知识模块开发.pdf
Contents
Preface
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 an Interface
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 Interface
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 an Interface
3.6.1 Loading Data From Files
3.6.2 Integrating Data in Files
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 an Interface
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 Interface
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 an Interface
5.6.1 Notes about XML Interfaces
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 an Interface
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 an Interface
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 an Interface
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 Microsoft Access
9.1 Introduction
9.2 Concepts
9.3 Knowledge Modules
9.4 Specific Requirements
10 Netezza
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 Netezza Data Server
10.3.1.1 Creation of the Data Server
10.3.2 Creating a Netezza Physical Schema
10.4 Setting Up an Integration Project
10.5 Creating and Reverse-Engineering a Netezza Model
10.5.1 Create a Netezza Model
10.5.2 Reverse-engineer a Netezza Model
10.6 Setting up Data Quality
10.7 Designing an Interface
10.7.1 Loading Data from and to Netezza
10.7.1.1 Loading Data from Netezza
10.7.1.2 Loading Data to Netezza
10.7.2 Integrating Data in Netezza
11 Teradata
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 Teradata Data Server
11.3.1.1 Creation of the Data Server
11.3.2 Creating a Teradata Physical Schema
11.4 Setting Up an Integration Project
11.5 Creating and Reverse-Engineering a Teradata Model
11.5.1 Create a Teradata Model
11.5.2 Reverse-engineer a Teradata Model
11.6 Setting up Data Quality
11.7 Designing an Interface
11.7.1 Loading Data from and to Teradata
11.7.1.1 Loading Data from Teradata
11.7.1.2 Loading Data to Teradata
11.7.2 Integrating Data in Teradata
11.7.3 Designing an ETL-Style Interface
11.8 KM Optimizations for Teradata
11.8.1 Primary Indexes and Statistics
11.8.2 Support for Teradata Utilities
11.8.3 Support for Named Pipes
11.8.4 Optimized Management of Temporary Tables
12 Hypersonic SQL
12.1 Introduction
12.1.1 Concepts
12.1.2 Knowledge Modules
12.2 Installation and Configuration
12.2.1 System Requirements and Certifications
12.2.2 Technology Specific Requirements
12.2.3 Connectivity Requirements
12.3 Setting up the Topology
12.3.1 Creating a Hypersonic SQL Data Server
12.3.2 Creating a Hypersonic SQL Physical Schema
12.4 Setting Up an Integration Project
12.5 Creating and Reverse-Engineering a Hypersonic SQL Model
12.5.1 Create a Hypersonic SQL Model
12.5.2 Reverse-engineer a Hypersonic SQL Model
12.6 Setting up Changed Data Capture
12.7 Setting up Data Quality
12.8 Designing an Interface
13 IBM Informix
13.1 Introduction
13.2 Concepts
13.3 Knowledge Modules
13.4 Specific Requirements
14 IBM DB2 for iSeries
14.1 Introduction
14.1.1 Concepts
14.1.2 Knowledge Modules
14.2 Installation and Configuration
14.2.1 System Requirements and Certifications
14.2.2 Technology Specific Requirements
14.2.3 Connectivity Requirements
14.3 Setting up the Topology
14.3.1 Creating a DB2/400 Data Server
14.3.1.1 Creation of the Data Server
14.3.2 Creating a DB2/400 Physical Schema
14.4 Setting Up an Integration Project
14.5 Creating and Reverse-Engineering an IBM DB2/400 Model
14.5.1 Create an IBM DB2/400 Model
14.5.2 Reverse-engineer an IBM DB2/400 Model
14.6 Setting up Changed Data Capture
14.6.1 Setting up Trigger-Based CDC
14.6.2 Setting up Log-Based CDC
14.6.2.1 How does it work?
14.6.2.2 CDCRTVJRN Program Details
14.6.2.3 Installing the CDC Components on iSeries
14.6.2.4 Using the CDC with the Native Journals
14.6.2.5 Problems While Reading Journals
14.7 Setting up Data Quality
14.8 Designing an Interface
14.8.1 Loading Data from and to IBM DB2 for iSeries
14.8.1.1 Loading Data from IBM DB2 for iSeries
14.8.1.2 Loading Data to IBM DB2 for iSeries
14.8.2 Integrating Data in IBM DB2 for iSeries
14.9 Specific Considerations with DB2 for iSeries
14.9.1 Installing the Run-Time Agent on iSeries
14.9.2 Alternative Connectivity Methods for iSeries
14.9.2.1 Using Client Access
14.9.2.2 Using the IBM JT/400 and Native Drivers
14.10 Troubleshooting
14.10.1 Troubleshooting Error messages
14.10.2 Common Problems and Solutions
14.10.2.1 Connection Errors
15 IBM DB2 UDB
15.1 Introduction
15.2 Concepts
15.3 Knowledge Modules
15.4 Specific Requirements
16 Sybase AS Enterprise
16.1 Introduction
16.2 Concepts
16.3 Knowledge Modules
16.4 Specific Requirements
17 Sybase IQ
17.1 Introduction
17.2 Concepts
17.3 Knowledge Modules
17.4 Specific Requirements
Part II Business Intelligence
18 Oracle Business Intelligence Enterprise Edition
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 an Oracle BI Data Server
18.3.1.1 Creation of the Data Server
18.3.2 Creating an Oracle BI Physical Schema
18.4 Setting Up an Integration Project
18.5 Creating and Reverse-Engineering an Oracle BI Model
18.5.1 Create an Oracle BI Model
18.5.2 Reverse-engineer an Oracle BI Model
18.6 Setting up Data Quality
18.7 Designing an Interface
18.7.1 Loading Data from and to Oracle BI
18.7.1.1 Loading Data from Oracle BI
18.7.1.2 Loading Data to Oracle BI
18.7.2 Integrating Data in Oracle BI
19 Oracle Business Intelligence Enterprise Edition Data Lineage
19.1 Introduction
19.1.1 Components
19.1.2 Lineage Lifecycle
19.1.2.1 Setting up the Lineage
19.1.2.2 Refreshing the Lineage
19.1.2.3 Using the Lineage
19.2 Installing the Lineage in an OBIEE Server
19.2.1 Installation Overview
19.2.2 Requirements
19.2.3 Installation Instructions
19.2.3.1 Installing and Starting the OBIEE Lineage Wizard
19.2.3.2 Deploying the OBIEE Lineage Artifacts using the Wizard
19.2.4 Post-Installation Tasks
19.3 Exporting Metadata from OBIEE and Refreshing the OBIEE Lineage
19.4 Refreshing the OBIEE Lineage from Existing Exports
19.4.1 Exporting the OBIEE Repository Documentation to a Text File
19.4.2 Exporting the OBIEE Web Catalog Report to a Text File
19.4.3 Refreshing the OBIEE Lineage From Existing Exports
19.5 Automating the Lineage Tasks
19.5.1 Configuring the Scripts
19.5.2 Automating Lineage Deployment
19.5.3 Automating Lineage Refresh
19.6 Using the Lineage in OBIEE Dashboards
19.6.1 Viewing Execution Statistics
19.6.2 Viewing and Filtering Lineage Data
19.6.3 Using the Dashboard
19.6.4 Using Lineage and Hierarchy
19.6.5 Using Contextual Lineage
20 Oracle Hyperion Essbase
20.1 Introduction
20.1.1 Integration 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 Setting up the Topology
20.3.1 Creating an Hyperion Essbase Data Server
20.3.2 Creating an Hyperion Essbase Physical Schema
20.4 Creating and Reverse-Engineering an Essbase Model
20.4.1 Create an Essbase Model
20.4.2 Reverse-engineer an Essbase Model
20.5 Designing an Interface
20.5.1 Loading Metadata
20.5.2 Loading Data
20.5.3 Extracting Data
20.5.3.1 Data Extraction Methods for Essbase
20.5.3.2 Extracting Essbase Data
20.5.3.3 Extracting Members from Metadata
21 Oracle Hyperion Financial Management
21.1 Introduction
21.1.1 Integration Process
21.1.2 Knowledge Modules
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 Setting up the Topology
21.3.1 Creating an Hyperion Financial Management Data Server
21.3.2 Creating an Hyperion Financial Management Physical Schema
21.4 Creating and Reverse-Engineering a Financial Management Model
21.4.1 Create an Financial Management Model
21.4.2 Reverse-Engineer an Financial Management Model
21.5 Designing an Interface
21.5.1 Loading Metadata
21.5.2 Loading Data
21.5.3 Extracting Data
21.5.3.1 Extracting Financial Management Data
21.5.3.2 Extracting Members from Member Lists
21.6 Data Store Tables
22 Oracle Hyperion Planning
22.1 Introduction
22.1.1 Integration Process
22.1.2 Knowledge Modules
22.2 Installation and Configuration
22.2.1 System Requirements and Certifications
22.2.2 Technology Specific Requirements
22.2.3 Connectivity Requirements
22.3 Setting up the Topology
22.3.1 Creating an Hyperion Planning Data Server
22.3.2 Creating an Hyperion Planning Physical Schema
22.4 Creating and Reverse-Engineering a Planning Model
22.4.1 Create a Planning Model
22.4.2 Reverse-engineer a Planning Model
22.5 Designing an Interface
22.5.1 Loading Metadata
22.5.2 Loading Data
22.5.3 Load Options
22.6 Datastore Tables and Data Load Columns
22.6.1 Accounts
22.6.2 Employee
22.6.3 Entities
22.6.4 User-Defined Dimensions
22.6.5 Attribute Dimensions
22.6.6 UDA
22.6.7 Data Load Columns
23 Oracle OLAP
23.1 Introduction
23.1.1 Concepts
23.1.2 Knowledge Modules
23.2 Installation and Configuration
23.2.1 System Requirements and Certifications
23.2.2 Technology Specific Requirements
23.2.3 Connectivity Requirements
23.3 Setting up the Topology
23.3.1 Creating an Oracle Data Server
23.3.1.1 Creation of the Data Server
23.3.2 Creating an Oracle Physical Schema
23.4 Setting Up an Integration Project
23.5 Creating and Reverse-Engineering an Oracle Model
23.5.1 Create an Oracle Model
23.5.2 Reverse-engineer an Oracle OLAP Cube
23.6 Working with Oracle OLAP KMs in Integration Interfaces
23.6.1 Using Oracle OLAP as a Source in an Integration Interface
23.6.2 Using Oracle ROLAP as a Target in an Integration Interface
23.6.3 Using Oracle MOLAP as a Target in an Integration Interface
Part III Other Technologies
24 JMS
24.1 Introduction
24.1.1 Concepts
24.1.1.1 JMS Message Structure
24.1.1.2 Using a JMS Destination
24.1.2 Knowledge Modules
24.2 Installation and Configuration
24.2.1 System Requirements and Certifications
24.2.2 Technology Specific Requirements
24.2.3 Connectivity Requirements
24.3 Setting up the Topology
24.3.1 Creating a JMS Data Server
24.3.1.1 Creation of the Data Server
24.3.2 Creating a JMS Physical Schema
24.4 Setting Up an Integration Project
24.5 Creating and Defining a JMS Model
24.5.1 Create a JMS Model
24.5.2 Defining the JMS Datastores
24.6 Designing an Interface
24.6.1 Loading Data from a JMS Source
24.6.2 Integrating Data in a JMS Target
24.7 JMS Standard Properties
24.7.1 Using JMS Properties
24.7.1.1 Declaring JMS Properties
24.7.1.2 Filtering on the Router
24.7.1.3 Filtering on the Client
24.7.1.4 Using Property Values as Source Data
24.7.1.5 Setting Properties when Sending a Message
25 JMS XML
25.1 Introduction
25.1.1 Concepts
25.1.1.1 JMS Message Structure
25.1.1.2 Using a JMS Destination
25.1.2 Knowledge Modules
25.2 Installation and Configuration
25.2.1 System Requirements and Certifications
25.2.2 Technology Specific Requirements
25.2.3 Connectivity Requirements
25.3 Setting up the Topology
25.3.1 Creating a JMS XML Data Server
25.3.1.1 Creation of the Data Server
25.3.2 Creating a JMS XML Physical Schema
25.4 Setting Up an Integration Project
25.5 Creating and Reverse-Engineering a JMS XML Model
25.5.1 Create a JMS XML Model
25.5.2 Reverse-Engineering a JMS XML Model
25.6 Designing an Interface
25.6.1 Loading Data from a JMS XML Source
25.6.2 Integrating Data in a JMS XML Target
26 LDAP Directories
26.1 Introduction
26.1.1 Concepts
26.1.2 Knowledge Modules
26.2 Installation and Configuration
26.2.1 System Requirements
26.2.2 Technologic Specific Requirements
26.2.3 Connectivity Requirements
26.3 Setting up the Topology
26.3.1 Creating an LDAP Data Server
26.3.1.1 Creation of the Data Server
26.3.2 Creating a Physical Schema for LDAP
26.4 Setting Up an Integration Project
26.5 Creating and Reverse-Engineering an LDAP Directory
26.5.1 Create an LDAP Model
26.5.2 Reverse-Engineering an LDAP Model
26.6 Designing an Interface
26.6.1 Loading Data from and to LDAP
26.6.1.1 Loading Data from an LDAP Directory
26.6.1.2 Loading Data to an LDAP Directory
26.6.2 Integrating Data in an LDAP Directory
26.7 Troubleshooting
27 Oracle Changed Data Capture Adapters
27.1 Introduction
27.1.1 Concepts
27.1.2 Knowledge Modules
27.2 Installation and Configuration
27.2.1 System Requirements
27.2.2 Technology Specific Requirements
27.2.3 Connectivity Requirements
27.3 Setting up the Topology
27.3.1 Creating an Attunity Stream Data Server
27.3.1.1 Creation of the Data Server
27.3.2 Creating an Attunity Stream Physical Schema
27.4 Setting Up an Integration Project
27.5 Creating and Reverse-Engineering an Attunity Stream Model
27.5.1 Create an Attunity Stream Model
27.5.2 Reverse-engineer an Attunity Stream Model
27.6 Designing an Interface Using the LKM Attunity to SQL
28 Oracle GoldenGate
28.1 Introduction
28.1.1 Overview of the GoldeGate CDC Process
28.1.2 Knowledge Modules
28.2 Installation and Configuration
28.2.1 System Requirements and Certifications
28.2.2 Technology Specific Requirements
28.2.3 Connectivity Requirements
28.3 Working with the Oracle GoldenGate JKMs
28.3.1 Define the Topology
28.3.1.1 Define the Staging Server
28.3.1.2 Create the Staging Physical Schema
28.3.1.3 Define the Source Data Server
28.3.1.4 Create the Source Physical Schema
28.3.2 Create the Replicated Tables
28.3.3 Set Up an Integration Project
28.3.4 Configure CDC for the Replicated Tables
28.3.5 Configure and Start Oracle GoldenGate Processes
28.3.6 Design Interfaces Using Replicated Data
28.4 Advanced Configuration
28.4.1 Initial Load Method
28.4.2 Tuning Replication Performances
28.4.3 One Source Multiple Staging Configuration
29 Oracle Mediator
29.1 Introduction
29.1.1 Concepts
29.1.2 Knowledge Modules
29.1.3 Overview of the SOA XREF KM Process
29.2 Installation and Configuration
29.2.1 System Requirements and Certifications
29.2.2 Technology Specific Requirements
29.2.3 Connectivity Requirements
29.3 Working with XREF using the SOA Cross-References KMs
29.3.1 Defining the Topology
29.3.2 Setting up the Project
29.3.3 Designing an Interface with the Cross-References KMs
29.4 Knowledge Module Options Reference
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 XMLFILE
B.4.3 CREATE FOREIGNKEYS
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.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
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
Oracle® Fusion Middleware
Connectivity and Knowledge Modules Guide for Oracle Data
Integrator
11g Release 1 (11.1.1)
E12644-04
April 2011
Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator, 11g
Release 1 (11.1.1)
E12644-04
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Laura Hofman Miquel
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify,
license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means.
Reverse engineering, disassembly, or decompilation of this software, unless required by law for
interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical
data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental
regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the
restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable
by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial
Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA
94065.
This software is developed for general use in a variety of information management applications. It is not
developed or intended for use in any inherently dangerous applications, including applications which may
create a risk of personal injury. If you use this software in dangerous applications, then you shall be
responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use
of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of
this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks
of their respective owners.
This software and documentation may provide access to or information on content, products, and services
from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all
warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and
its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of
third-party content, products, or services.
Contents
Preface .............................................................................................................................................................. xvii
Audience.................................................................................................................................................... xvii
Documentation Accessibility.................................................................................................................. xvii
Related Documents ................................................................................................................................. xviii
Conventions ............................................................................................................................................. xviii
1 Introduction
1.1
1.2
Terminology................................................................................................................................. 1-1
Using This Guide ........................................................................................................................ 1-2
Part I Databases, Files, and XML
2 Oracle Database
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.2.3
2.3
2.3.1
2.3.2
2.4
2.5
2.5.1
2.5.2
2.6
2.7
2.8
2.8.1
2.8.2
2.8.3
2.9
2.9.1
Introduction ................................................................................................................................. 2-1
Concepts................................................................................................................................ 2-1
Knowledge Modules ........................................................................................................... 2-1
Installation and Configuration.................................................................................................. 2-3
System Requirements and Certifications ......................................................................... 2-3
Technology Specific Requirements ................................................................................... 2-3
Connectivity Requirements................................................................................................ 2-5
Setting up the Topology............................................................................................................. 2-6
Creating an Oracle Data Server ......................................................................................... 2-6
Creating an Oracle Physical Schema................................................................................. 2-6
Setting Up an Integration Project ............................................................................................. 2-7
Creating and Reverse-Engineering an Oracle Model ............................................................ 2-7
Create an Oracle Model ...................................................................................................... 2-7
Reverse-engineer an Oracle Model ................................................................................... 2-7
Setting up Changed Data Capture ........................................................................................... 2-8
Setting up Data Quality ............................................................................................................. 2-9
Designing an Interface ............................................................................................................... 2-9
Loading Data from and to Oracle................................................................................... 2-10
Integrating Data in Oracle ............................................................................................... 2-10
Designing an ETL-Style Interface................................................................................... 2-12
Troubleshooting ....................................................................................................................... 2-15
Troubleshooting Oracle Database Errors ...................................................................... 2-15
iii
2.9.2
Common Problems and Solutions.................................................................................. 2-15
3 Files
3.1
3.1.1
3.1.2
3.2
3.2.1
3.2.2
3.2.3
3.3
3.3.1
3.3.2
3.4
3.5
3.5.1
3.5.2
3.6
3.6.1
3.6.2
Introduction ................................................................................................................................. 3-1
Concepts................................................................................................................................ 3-1
Knowledge Modules ........................................................................................................... 3-1
Installation and Configuration.................................................................................................. 3-2
System Requirements and Certifications ......................................................................... 3-2
Technology Specific Requirements ................................................................................... 3-2
Connectivity Requirements................................................................................................ 3-3
Setting up the Topology............................................................................................................. 3-3
Creating a File Data Server................................................................................................. 3-3
Creating a File Physical Schema ........................................................................................ 3-4
Setting Up an Integration Project ............................................................................................. 3-4
Creating and Reverse-Engineering a File Model ................................................................... 3-5
Create a File Model.............................................................................................................. 3-5
Reverse-engineer a File Model........................................................................................... 3-5
Designing an Interface................................................................................................................ 3-9
Loading Data From Files .................................................................................................... 3-9
Integrating Data in Files .................................................................................................. 3-11
4 Generic SQL
4.1
4.1.1
4.1.2
4.2
4.2.1
4.2.2
4.2.3
4.3
4.3.1
4.3.2
4.4
4.5
4.5.1
4.5.2
4.6
4.7
4.8
4.8.1
4.8.2
4.8.3
Introduction ................................................................................................................................. 4-1
Concepts................................................................................................................................ 4-1
Knowledge Modules ........................................................................................................... 4-2
Installation and Configuration.................................................................................................. 4-4
System Requirements and Certifications ......................................................................... 4-4
Technology-Specific Requirements................................................................................... 4-4
Connectivity Requirements................................................................................................ 4-5
Setting up the Topology............................................................................................................. 4-5
Creating a Data Server ........................................................................................................ 4-5
Creating a Physical Schema ............................................................................................... 4-5
Setting up an Integration Project .............................................................................................. 4-5
Creating and Reverse-Engineering a Model........................................................................... 4-5
Create a Data Model............................................................................................................ 4-6
Reverse-engineer a Data Model......................................................................................... 4-6
Setting up Changed Data Capture ........................................................................................... 4-6
Setting up Data Quality.............................................................................................................. 4-6
Designing an Interface ............................................................................................................... 4-7
Loading Data From and to an ANSI SQL-92 Compliant Technology ......................... 4-7
Integrating Data in an ANSI SQL-92 Compliant Technology....................................... 4-8
Designing an ETL-Style Interface...................................................................................... 4-8
5 XML Files
5.1
5.1.1
Introduction ................................................................................................................................. 5-1
Concepts................................................................................................................................ 5-1
iv
5.1.2
5.2
5.2.1
5.2.2
5.2.3
5.3
5.3.1
5.3.2
5.4
5.5
5.5.1
5.5.2
5.6
5.6.1
5.6.2
5.6.3
5.7
5.7.1
5.7.2
Knowledge Modules ........................................................................................................... 5-2
Installation and Configuration.................................................................................................. 5-2
System Requirements.......................................................................................................... 5-2
Technologic Specific Requirements .................................................................................. 5-2
Connectivity Requirements................................................................................................ 5-2
Setting up the Topology............................................................................................................. 5-2
Creating an XML Data Server............................................................................................ 5-3
Creating a Physical Schema for XML ............................................................................... 5-4
Setting Up an Integration Project ............................................................................................. 5-4
Creating and Reverse-Engineering a XML File ...................................................................... 5-4
Create an XML Model ......................................................................................................... 5-5
Reverse-Engineering an XML Model................................................................................ 5-5
Designing an Interface ............................................................................................................... 5-5
Notes about XML Interfaces............................................................................................... 5-5
Loading Data from and to XML ........................................................................................ 5-6
Integrating Data in XML..................................................................................................... 5-7
Troubleshooting .......................................................................................................................... 5-8
Detect the Errors Coming from XML................................................................................ 5-8
Common Errors.................................................................................................................... 5-8
6 Complex Files
6.1
6.1.1
6.1.2
6.2
6.2.1
6.2.2
6.2.3
6.3
6.3.1
6.3.2
6.4
6.5
6.5.1
6.5.2
6.6
Introduction ................................................................................................................................. 6-1
Concepts................................................................................................................................ 6-1
Knowledge Modules ........................................................................................................... 6-2
Installation and Configuration.................................................................................................. 6-2
System Requirements.......................................................................................................... 6-2
Technology Specific Requirements ................................................................................... 6-3
Connectivity Requirements................................................................................................ 6-3
Setting up the Topology............................................................................................................. 6-3
Creating a Complex File Data Server................................................................................ 6-3
Creating a Complex File Physical Schema....................................................................... 6-4
Setting Up an Integration Project ............................................................................................. 6-4
Creating and Reverse-Engineering a Complex File Model .................................................. 6-5
Create a Complex File Model............................................................................................. 6-5
Reverse-engineer a Complex File Model.......................................................................... 6-5
Designing an Interface ............................................................................................................... 6-5
7 Microsoft SQL Server
7.1
7.1.1
7.1.2
7.2
7.2.1
7.2.2
7.2.3
7.3
Introduction ................................................................................................................................. 7-1
Concepts................................................................................................................................ 7-1
Knowledge Modules ........................................................................................................... 7-1
Installation and Configuration.................................................................................................. 7-2
System Requirements and Certifications ......................................................................... 7-2
Technology Specific Requirements ................................................................................... 7-3
Connectivity Requirements................................................................................................ 7-4
Setting up the Topology............................................................................................................. 7-4
v
7.3.1
7.3.2
7.4
7.5
7.5.1
7.5.2
7.6
7.7
7.8
7.8.1
7.8.2
Creating a Microsoft SQL Server Data Server ................................................................. 7-4
Creating a Microsoft SQL Server Physical Schema ........................................................ 7-5
Setting Up an Integration Project ............................................................................................. 7-5
Creating and Reverse-Engineering a Microsoft SQL Server Model.................................... 7-5
Create a Microsoft SQL Server Model .............................................................................. 7-5
Reverse-engineer a Microsoft SQL Server Model ........................................................... 7-6
Setting up Changed Data Capture ........................................................................................... 7-6
Setting up Data Quality.............................................................................................................. 7-7
Designing an Interface................................................................................................................ 7-7
Loading Data from and to Microsoft SQL Server ........................................................... 7-7
Integrating Data in Microsoft SQL Server........................................................................ 7-9
8 Microsoft Excel
8.1
8.1.1
8.1.2
8.2
8.2.1
8.2.2
8.2.3
8.3
8.3.1
8.3.2
8.4
8.5
8.5.1
8.5.2
8.6
8.6.1
8.6.2
8.7
8.7.1
8.7.2
Introduction ................................................................................................................................. 8-1
Concepts................................................................................................................................ 8-1
Knowledge Modules ........................................................................................................... 8-1
Installation and Configuration.................................................................................................. 8-2
System Requirements and Certifications ......................................................................... 8-2
Technology Specific Requirements ................................................................................... 8-2
Connectivity Requirements................................................................................................ 8-2
Setting up the Topology............................................................................................................. 8-3
Creating a Microsoft Excel Data Server............................................................................ 8-3
Creating a Microsoft Excel Physical Schema ................................................................... 8-3
Setting Up an Integration Project ............................................................................................. 8-4
Creating and Reverse-Engineering a Microsoft Excel Model............................................... 8-4
Create a Microsoft Excel Model......................................................................................... 8-4
Reverse-engineer a Microsoft Excel Model...................................................................... 8-4
Designing an Interface................................................................................................................ 8-5
Loading Data From and to Microsoft Excel..................................................................... 8-5
Integrating Data in Microsoft Excel .................................................................................. 8-6
Troubleshooting .......................................................................................................................... 8-6
Decoding Error Messages................................................................................................... 8-6
Common Problems and Solutions..................................................................................... 8-6
9 Microsoft Access
9.1
9.2
9.3
9.4
Introduction ................................................................................................................................. 9-1
Concepts ....................................................................................................................................... 9-1
Knowledge Modules .................................................................................................................. 9-1
Specific Requirements ................................................................................................................ 9-2
10 Netezza
10.1
10.1.1
10.1.2
10.2
10.2.1
Introduction .............................................................................................................................. 10-1
Concepts............................................................................................................................. 10-1
Knowledge Modules ........................................................................................................ 10-1
Installation and Configuration............................................................................................... 10-2
System Requirements and Certifications ...................................................................... 10-2
vi
10.2.2
10.2.3
10.3
10.3.1
10.3.2
10.4
10.5
10.5.1
10.5.2
10.6
10.7
10.7.1
10.7.2
Technology Specific Requirements ................................................................................ 10-2
Connectivity Requirements............................................................................................. 10-3
Setting up the Topology.......................................................................................................... 10-3
Creating a Netezza Data Server...................................................................................... 10-3
Creating a Netezza Physical Schema ............................................................................. 10-3
Setting Up an Integration Project .......................................................................................... 10-4
Creating and Reverse-Engineering a Netezza Model ........................................................ 10-4
Create a Netezza Model................................................................................................... 10-4
Reverse-engineer a Netezza Model................................................................................ 10-4
Setting up Data Quality .......................................................................................................... 10-5
Designing an Interface ............................................................................................................ 10-5
Loading Data from and to Netezza................................................................................ 10-5
Integrating Data in Netezza ............................................................................................ 10-6
11 Teradata
11.1
11.1.1
11.1.2
11.2
11.2.1
11.2.2
11.2.3
11.3
11.3.1
11.3.2
11.4
11.5
11.5.1
11.5.2
11.6
11.7
11.7.1
11.7.2
11.7.3
11.8
11.8.1
11.8.2
11.8.3
11.8.4
Introduction .............................................................................................................................. 11-1
Concepts............................................................................................................................. 11-1
Knowledge Modules ........................................................................................................ 11-1
Installation and Configuration............................................................................................... 11-2
System Requirements and Certifications ...................................................................... 11-2
Technology Specific Requirements ................................................................................ 11-3
Connectivity Requirements............................................................................................. 11-3
Setting up the Topology.......................................................................................................... 11-3
Creating a Teradata Data Server .................................................................................... 11-4
Creating a Teradata Physical Schema............................................................................ 11-4
Setting Up an Integration Project .......................................................................................... 11-4
Creating and Reverse-Engineering a Teradata Model ....................................................... 11-5
Create a Teradata Model.................................................................................................. 11-5
Reverse-engineer a Teradata Model .............................................................................. 11-5
Setting up Data Quality .......................................................................................................... 11-6
Designing an Interface ............................................................................................................ 11-6
Loading Data from and to Teradata .............................................................................. 11-6
Integrating Data in Teradata........................................................................................... 11-8
Designing an ETL-Style Interface................................................................................. 11-12
KM Optimizations for Teradata........................................................................................... 11-15
Primary Indexes and Statistics...................................................................................... 11-15
Support for Teradata Utilities ....................................................................................... 11-16
Support for Named Pipes.............................................................................................. 11-16
Optimized Management of Temporary Tables ......................................................... 11-17
12 Hypersonic SQL
12.1
12.1.1
12.1.2
12.2
12.2.1
Introduction .............................................................................................................................. 12-1
Concepts............................................................................................................................. 12-1
Knowledge Modules ........................................................................................................ 12-1
Installation and Configuration............................................................................................... 12-2
System Requirements and Certifications ...................................................................... 12-2
vii
12.2.2
12.2.3
12.3
12.3.1
12.3.2
12.4
12.5
12.5.1
12.5.2
12.6
12.7
12.8
Technology Specific Requirements ................................................................................ 12-2
Connectivity Requirements............................................................................................. 12-2
Setting up the Topology.......................................................................................................... 12-2
Creating a Hypersonic SQL Data Server....................................................................... 12-3
Creating a Hypersonic SQL Physical Schema .............................................................. 12-3
Setting Up an Integration Project .......................................................................................... 12-3
Creating and Reverse-Engineering a Hypersonic SQL Model.......................................... 12-3
Create a Hypersonic SQL Model.................................................................................... 12-4
Reverse-engineer a Hypersonic SQL Model................................................................. 12-4
Setting up Changed Data Capture ........................................................................................ 12-4
Setting up Data Quality........................................................................................................... 12-4
Designing an Interface............................................................................................................. 12-5
13 IBM Informix
13.1
13.2
13.3
13.4
Introduction .............................................................................................................................. 13-1
Concepts .................................................................................................................................... 13-1
Knowledge Modules ............................................................................................................... 13-1
Specific Requirements ............................................................................................................. 13-2
14 IBM DB2 for iSeries
14.1
Introduction .............................................................................................................................. 14-1
14.1.1
Concepts............................................................................................................................. 14-1
14.1.2
Knowledge Modules ........................................................................................................ 14-2
14.2
Installation and Configuration............................................................................................... 14-2
14.2.1
System Requirements and Certifications ...................................................................... 14-2
14.2.2
Technology Specific Requirements ................................................................................ 14-3
14.2.3
Connectivity Requirements............................................................................................. 14-3
14.3
Setting up the Topology.......................................................................................................... 14-3
14.3.1
Creating a DB2/400 Data Server .................................................................................... 14-3
14.3.2
Creating a DB2/400 Physical Schema............................................................................ 14-4
14.4
Setting Up an Integration Project .......................................................................................... 14-4
14.5
Creating and Reverse-Engineering an IBM DB2/400 Model ............................................ 14-4
14.5.1
Create an IBM DB2/400 Model ...................................................................................... 14-5
14.5.2
Reverse-engineer an IBM DB2/400 Model ................................................................... 14-5
14.6
Setting up Changed Data Capture ........................................................................................ 14-5
14.6.1
Setting up Trigger-Based CDC ....................................................................................... 14-5
14.6.2
Setting up Log-Based CDC.............................................................................................. 14-6
14.7
Setting up Data Quality........................................................................................................... 14-9
14.8
Designing an Interface............................................................................................................. 14-9
14.8.1
Loading Data from and to IBM DB2 for iSeries ........................................................ 14-10
14.8.2
Integrating Data in IBM DB2 for iSeries ..................................................................... 14-11
14.9
Specific Considerations with DB2 for iSeries..................................................................... 14-11
14.9.1
Installing the Run-Time Agent on iSeries ................................................................... 14-11
14.9.2
Alternative Connectivity Methods for iSeries ............................................................ 14-11
14.10 Troubleshooting ..................................................................................................................... 14-12
Troubleshooting Error messages.................................................................................. 14-12
14.10.1
14.10.2
Common Problems and Solutions................................................................................ 14-13
viii
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
