Oracle Data Integrator 11g Cookbook

Oracle Data Integrator 11g Cookbook

Overview of this book

Oracle Data Integrator (ODI) is Oracle's strategic data integration platform for high-speed data transformation and movement between different systems. From high-volume batches, to SOA-enabled data services, to trickle operations, ODI is a cutting-edge platform that offers heterogeneous connectivity, enterprise-level deployment, and strong administrative, diagnostic, and management capabilities."Oracle Data Integrator 11g Cookbook" will take you on a journey past your first steps with ODI to a new level of proficiency, lifting the cover on many of the internals of the product to help you better leverage the most advanced features.The first part of this book will focus on the administrative tasks required for a successful deployment, moving on to showing you how to best leverage Knowledge Modules with explanations of their internals and focus on specific examples. Next we will look into some advanced coding techniques for interfaces, packages, models, and a focus on XML. Finally the book will lift the cover on web services as well as the ODI SDK, along with additional advanced techniques that may be unknown to many users.Throughout "Oracle Data Integrator 11g Cookbook", the authors convey real-world advice and best practices learned from their extensive hands-on experience.

Oracle Data Integrator 11g Cookbook

Credits

Foreword

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Installation, Deployment, and Configuration

Introduction

Deploying and configuring a standalone agent manually

Deploying a JEE ODI Agent

Configuring a standalone agent with OPMN

Deploying JDBC drivers with a JEE ODI Agent

Defining the Oracle Data Integrator Security

Introduction

Setting up LDAP security for Oracle Data Integrator

Setting external authentication with Oracle Data Integrator

Creating users using generic profiles

Creating users using non-generic profiles

Creating new custom profiles in the Security navigator

Advanced Topology

Introduction

Creating a new technology

Modifying actions to get more from your diagrams

Modifying and expanding datatypes

Changing the case sensitivity for code generation

Best practice – using the Staging Area User to access your target schema

Using Variables

Introduction

Passing start-up parameters to a scenario using variables

Using table names that run in all contexts using getObjectName

Using variables to filter data based on a timestamp

Using variables in KM options (and reusing the variables in an interface, package, and so on)

Using variables in topology

Using variables to control loops inside packages

Knowledge Module Internals

Introduction

Using the substitution passes

Using Java variables in KMs

Using Java for condition code generation

Invoking Java from the KMs

Using substitution methods in Java

Combining substitution methods in a KM

Inside Knowledge Modules – SCD and CDC

Introduction

Implementing Slowly Changing Dimensions (SCD) using ODI

Modifying a Slowly Changing Dimension KM to allow undefined behaviors

Using Changed Data Capture (CDC) - simple

Using Changed Data Capture (CDC) - consistent set

Using one single interface to load changes that occur in any dimensions

Advanced Coding Techniques

Introduction

Using diagrams to develop and maintain models

Generating DDL from data models

Generating interfaces from data models or diagrams

Creating a temporary interface (subquery)

Loading data from an SQL query

Performing a pivot

Loading data using partition exchange

Package Loops and File Processing

Introduction

Defining packages and loops for near real-time execution using a hybrid loop

Using a file from a parameter variable

Detecting files with a variable name

Processing all files in a directory

Processing a large number of files in parallel

XML and Web Services

Introduction

Defining a connection to XML within ODI

Processing complex files with ODI

Processing XML data within an RDBMS not in memory

Invoking web services from ODI

Invoking asynchronous ODI web services with callbacks

Configuring container-based authentication with ODI web services

Advanced Coding Techniques Using the ODI SDK

Introduction

Creating the Master and Work repositories using the SDK

Creating a project using the SDK

Automating the import of artifacts using the SDK

Creating models and datastores using the SDK

Creating an interface using the SDK

Invoking and monitoring a scenario using the SDK

Configuring a standalone agent with OPMN

Oracle Process Manager and Notification Server (OPMN) provides the ability to manage the lifecycle of ODI standalone agents. Using OPMN Oracle Data Integrator, users can control and monitor the status of standalone agents in Oracle Enterprise Manager. In addition, OPMN can automatically restart ODI standalone agents in case of failures.

Getting ready

In this recipe, we will be using the Oracle Web Tier Utilities installer, which contains OPMN. At the time of writing, the installer for Oracle Fusion Middleware Web Tier Utilities 11g can be downloaded from the Oracle Software Delivery Cloud (https://edelivery.oracle.com/) as part of the 'Oracle Fusion Middleware 11g Media Pack'. Make sure to download the right version for your operating system.

An agent named ODI_OPMN_AGENT needs to be defined as a Physical Agent in your ODI Topology.

No other prerequisites are required.

How to do it...

Start the Oracle Fusion Middleware Web Tier Utilities 11g installer from its installation directory.
In the Install and Configure section, select Install and Configure and click on Next.
In the Configure Components part of the installer, check the Oracle HTTP Server and Oracle Web Cache checkboxes and then click on Next.
In the Specify Component Details step, we use the default installation setting for the Instance Home Location, Instance Name, OHS Component Name, and Web Cache Component Name options:
Click on Next to go to the next installer screen.
In the Web Cache Administrator Password screen, enter the password of your choice. In this recipe, we use welcome1 and click on Next.
In the Configure Ports step, select Auto Port Configuration and click on Next.
Finally, click on Install in the Installation Summary screen to start the Oracle Web Tier Utilities installation process.
Once OPMN is installed, go to your ODI_HOME/oracledi/agent/bin/ directory and open up the agentcreate.properties file to edit it. The parameters in this file need to be modified to correspond to your ODI and OPMN configuration settings. In this recipe we use the following values:
```
ORACLE_ODI_HOME=C:/fmw/Oracle_ODI_1
INSTANCE_HOME=C:/fmw/Oracle_WT1/instances/instance1
COMPONENT_TYPE=odiagent
COMPONENT_NAME=ODI_OPMN_AGENT
ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl
ODI_MASTER_USER=ODI_REPO_11G
ODI_MASTER_ENCODED_PASS=dpfHbipjYbBAfm7P,Dt69kPBV
ODI_SECU_WORK_REPO=ODI_11G_WREP
ODI_SUPERVISOR_ENCODED_PASS=d,yXMMv0Rk6rgNVKEnBb1xE.E
PORTNO=20920
JAVA_HOME=C:/Java/jdk1.6.0_24
ORACLE_OPMN_HOME=C:/fmw/Oracle_WT1
JMXPORTNO=21920
```
The path values to provide such as ORACLE_ODI_HOME or INSTANCE_HOME must be by using forward slashes ('/') and not backward slashes ('\').
Note
The encoded passwords, such as ODI_MASTER_ENCODED_PASS or ODI_SUPERVISOR_ENCODED_PASS, can be obtained using the encode.bat or encode.sh command provided in the ODI_HOME/oracledi/agent/bin directory.

Next, open up the odi_opmn_addagent.bat or odi_opmn_addagent.sh script and enter the path values for ODI_HOME and OPMN_HOME. We use the following values in this recipe:

if "%ODI_HOME%" == "" set ODI_HOME=C:/fmw/Oracle_ODI_1/oracledi/agent
REM call "%ODI_HOME%\bin\odiparams.bat"

if "%OPMN_HOME%" == "" set OPMN_HOME=C:/fmw/Oracle_WT1
if "%INSTANCE_HOME%" == "" set INSTANCE_HOME=C:/fmw/Oracle_WT1/instances/instance1

Go to OPMN_HOME/instances/INSTANCE_NAME/bin, in which INSTANCE_NAME needs to be replaced by the OPMN instance name created earlier with the Web Tier Utilities installer (instance1 in our recipe).
Then run the following command to start OPMN:
```
opmnctl.bat start
```
Once OPMN is started, navigate to ODI_HOME/oracledi/agent/bin/ in your command prompt and run the following command to add a standalone agent to OPMN:
```
odi_opmn_addagent.bat
```
Subsequently, go back to OPMN_HOME/instances/INSTANCE_NAME/bin and enter the following command to verify that the ODI standalone agent named ODI_OPMN_AGENT is started:
```
opmnctl.bat status
```
The agent ODI_OPMN_AGENT has now been successfully started and is now managed by OPMN. You can test the connection to the agent from Topology Navigator in ODI Studio.

How it works...

The agentcreate.properties file contains all the required information for OPMN to manage a standalone agent. We'll take a look at some of those parameters:

ORACLE_ODI_HOME represents the path to the ODI installation directory
INSTANCE_HOME points to the OPMN instance home directory
COMPONENT_NAME is the name of the Physical Agent that will be managed through OPMN
ODI_MASTER_DRIVER and ODI_MASTER_URL respectively represent the class name of the JDBC driver and the JDBC URL used to connect to the Master repository
ODI_MASTER_USER and ODI_MASTER_ENCODED_PASS are the database username and encoded password required to access the Master repository
ODI_SECU_WORK contains the name of the Work Repository
ODI_SUPERVISOR_ENCODED_PASS stores the encoded ODI Supervisor user password
PORTNO corresponds to the port number the agent will be listening to
JMX_PORTNO is the Java Management Extensions (JMX) port number the agent will be using. JMX is used to propagate events from the agent to monitoring applications such as Oracle Enterprise Manager.

There's more...

The odi_opmn_deleteagent command allows users to remove agents that were previously added to the OPMN configuration settings. It is also possible to stop and restart the ODI agents' processes using the opmnctl command.

Oracle Data Integrator 11g Cookbook

Oracle Data Integrator 11g Cookbook

Overview of this book

Related Content you might be interested in

Current Title:

Oracle Data Integrator 11g Cookbook

Configuring a standalone agent with OPMN

Getting ready

How to do it...

Note

How it works...

There's more...