Home Business & Other Oracle Data Integrator 11g Cookbook

Oracle Data Integrator 11g Cookbook

books-svg-icon Book
eBook $36.99 $24.99
Print $60.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $36.99 $24.99
Print $60.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Installation, Deployment, and Configuration
About 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.
Publication date:
May 2013
Publisher
Packt
Pages
352
ISBN
9781849681742

 

Chapter 1. Installation, Deployment, and Configuration

The recipes in this chapter will help users learn the following:

  • Deploying and configuring a standalone agent manually

  • Deploying JEE ODI Agent

  • Configuring a standalone agent with OPMN

  • Deploying JDBC drivers with JEE ODI Agent

 

Introduction


Installation, deployment, and configuration are key to every successful software implementation. Oracle Data Integrator is no different; thoroughly understanding the different installation and deployment options will help in planning the overall topology for ODI.

This chapter provides quick-start recipes for installation, deployment, and configuration. For detailed information on each topic, please review the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator at http://docs.oracle.com/cd/E23943_01/core.1111/e16453/toc.htm, as well as the Fusion Middleware Developers Guide for Oracle Data Integrator located at http://docs.oracle.com/cd/E23943_01/integrate.1111/e12643/toc.htm.

Understanding the ODI Installation

Before jumping into the recipes, some background is needed on the ODI components:

  • ODI repositories: It consists of Master and Work repositories in which ODI metadata and data is stored

  • ODI Studio: It is used to develop ETL mappings as well as to administer and monitor the ODI

  • ODI standalone agent: The ODI runtime process that orchestrates ETL flows

  • JEE components:

    • Enterprise manager fusion middleware control plugin for ODI: It is used to monitor ODI from a web browser

    • Public web services: They are used to start and monitor scenarios through the JEE or a standalone agent

    • ODI console: It is used to execute and monitor ODI jobs as well as browse ODI metadata from a web browser

    • JEE agent: JEE enabled ODI agent that allows the ODI agent to inherently take advantage of the application server's enterprise features, such as high availability, connection pooling, and so on.

Each component can be installed and configured with the Oracle Universal Installer, which is delivered in the installation bundle that was downloaded from Oracle.com. The ODI Oracle Universal Installer has three choices for installing ODI. Each option is not exclusive; one option or all three options may be selected.

The first option is Developer Installation; this installation will install and configure ODI Studio and the ODI SDK. As you might guess, any ODI developer will need this installation type.

Note

The ODI Studio agent can be manually installed from the ODI Companion CD.

The second option is Standalone Installation; this installation installs and configures the standalone agent.

Note

The ODI standalone agent can be manually installed from the ODI Companion CD.

The third option is Java EE Installation. This option will install the ODI JEE files in an existing Oracle Middleware home. Each JEE component is configured during another process using the FMW configuration utility.

The ODI installation process has been extensively documented. Each enterprise deployment of ODI will vary, depending on many of the variables. The Oracle Universal Installer automates virtually every aspect of the ODI installation and guides the user through the installation process and configuration process. Additional configuration tasks are needed for the JEE components. Please refer to the ODI Installation guide for more information.

Although the Oracle Universal Installer does automate most ODI installation and configuration tasks, there are use cases when it is not feasible to use the Oracle Universal Installer, such as when the server where the ODI component is installed does not have graphical capabilities or if there is no platform specific installation for an OS. For this type of installation, many of the ODI components can be deployed manually. Deploying the ODI standalone agent is outlined in the next recipe.

 

Deploying and configuring a standalone agent manually


Performing a manual installation is required when the server on which you would like to set up your ODI standalone agent does not possess the graphical capabilities required to run the Oracle Universal Installer for Oracle Data Integrator, or when there is no installer available for the operating system platform you are working with. Manually installing a standalone agent should be done only when necessary, as bypassing the installer will not allow the application of patches, so proceed with caution.

Getting ready

In this recipe, we will be using files included in the ODI Companion CD. At the time of writing, it can be downloaded at http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html from the Oracle Technology Network. You can follow the instructions using your own repository. No other prerequisites are required.

How to do it...

  1. Unzip the content of the ODI Companion CD and then open up the /agent_standalone folder. Unzip the oracledi-agent-standalone.zip file to the location in which you would like to deploy your standalone agent.

  2. Go to the directory in which you extracted the files required to run your standalone agent, then navigate to the oracledi/agent/bin directory, and open up the odiparams.sh or odiparams.bat file using your favorite text editor.

  3. We will now modify some of the parameters included in the odiparams file starting with the Master repository information. Edit the file so that ODI_MASTER_DRIVER has the correct JDBC driver class name and the ODI_MASTER_URL value is a valid JDBC URL for the database hosting your Master repository. Finally, complete the Master repository configuration by providing the database username and its password using the ODI_MASTER_USER and ODI_MASTER_ENCODED_PASS parameters. We use the following values in this recipe:

    REM #
    REM # Repository Connection Information
    REM #
    set ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
    set ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl
    set ODI_MASTER_USER=ODI_REPO_11G
    set ODI_MASTER_ENCODED_PASS=dpfHbipjYbBAfm7P,Dt69kPBV
  4. Next we need to provide the name of the ODI Supervisor user along with its encoded password using the ODI_SUPERVISOR and ODI_SUPERVISOR_ENCODED_PASS variables.

    REM #
    REM # User credentials for agent startup program
    REM #
    set ODI_SUPERVISOR=SUPERVISOR
    set ODI_SUPERVISOR_ENCODED_PASS=d,yXMMv0Rk6rgNVKEnBb1xE.E

    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 agent/bin directory.

  5. Subsequently, we set the value of ODI_SECU_WORK_REP to the name of the Work repository that will be used by this standalone agent. The Work repository is named WORKREP in this recipe.

    REM #
    REM # Work Repository Name
    REM #
    set ODI_SECU_WORK_REP=WORKREP
  6. Optionally we can also change the Java Virtual Machine settings for this agent using the ODI_INIT_HEAP and ODI_MAX_HEAP parameters. Out of the box, the JVM settings are quite low, so it is a good idea to increase their values; the values below are not example settings:

    REM #
    REM # Other Parameters
    REM #
    set ODI_INIT_HEAP=256m
    set ODI_MAX_HEAP=1024m

    Note

    The ODI_INIT_HEAP and ODI_MAX_HEAP are recommended settings for this exercise. The required settings for each parameter depend on the actual aggregate memory requirements for all the integrations run on an agent.

  7. Now save the odiparams.sh or odiparams.bat file and close it.

  8. Open up ODI Studio and go to Topology Navigator.

  9. In Physical Architecture, right-click on the Agents node and select New Agent.

  10. In the Agent panel, specify the agent's name along with the machine hostname and its port. Save the settings.

  11. Open up a terminal and navigate to your agent's installation /bin directory.

  12. Start your standalone agent using the agent.sh or agent.bat script and by providing its name (same as the one specified in Topology) and its port. In this recipe we use the following command:

    agent.bat –NAME=LOCAL_STANDALONE_AGENT –PORT=20910
  13. Go back to Topology, open up your newly created Physical Agent, and click on Test to verify if all the parameters were entered correctly.

How it works...

The odiparams.sh or odiparams.bat file contains all the parameters required for a standalone agent to connect to an existing pair of Master and Work repositories. We will now highlight some of the parameters included in the file that were used in this recipe:

  • ODI_MASTER_DRIVER stores the class name of the JDBC driver used to access the Master repository database

  • ODI_MASTER_URL holds the JDBC URL utilized to connect to the Master repository database

  • ODI_MASTER_USER is the name of the database account for your Master repository

  • ODI_MASTER_ENCODED_PASS contains the encoded password of your Master repository database account

  • ODI_SUPERVISOR represents the name of the ODI Supervisor user

  • ODI_SUPERVISOR_ENCODED_PASS stores the encoded password of the ODI Supervisor user

There's more...

It is also possible to install a standalone agent using the Oracle Universal Installer for an ODI if you select the Standalone Installation option. The Oracle Data Integrator Installer has the capability to deploy a standalone agent and configure it automatically for a given Master and Work repositories pair.

 

Deploying a JEE ODI Agent


The ODI Agent can be deployed as a Java EE component within an application server. This installation type allows the ODI agent to take advantage of the benefits of an enterprise application server. When the ODI JEE Agent is deployed within Oracle WebLogic Server, the ODI JEE Agent can leverage the WebLogic's enterprise features, such as clustering and connection pooling for high availability and enterprise scalability. This Java EE Agent exposes an interface enabling lifecycle operation (start/stop) from the application server console and metrics that can be used by the application server console to monitor the agent activity and health.

The ODI 11g Java EE Agent can be deployed to an existing domain or deployed automatically when creating a new domain.

Note

Weblogic Server 10.1.3.6.0 is required for this recipe. Please review the latest Oracle Data Integrator Certification Matrix for the latest version compatibility.

This recipe will create a new WebLogic domain by deploying the Java EE Agent template.

Note

The standard ODI Java EE Agent template will be used to deploy our Java EE Agent. A template for any agent can also be generated from within ODI Studio and then used to deploy this agent.

How to do it...

  1. The Java EE Agent must exist in the ODI topology before the WLS domain server is started for the agent. Connect to ODI Studio, expand ODI Agents within the topology, and add an agent with the name OracleDIAgent and with the port 8001.

  2. To deploy and configure domains with WLS, execute config.bat or config.sh from within the ODI Home Install at Middleware_HOME\ODI_HOME\common\bin.

    Tip

    Downloading the example code

    You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

  3. Select the Create a new Weblogic domain option and click on Next.

  4. Select Oracle Data Integrator - Agent - 11.1.1.0 [Oracle_ODI1], which will additionally select the two options as shown in the following screenshot, and click on Next:

  5. For this recipe, accept the defaults and click on Next.

  6. Accept the default name as weblogic, set the password as weblogic1, and click on Next.

  7. Select SUN JDK and click on Next.

  8. Enter the appropriate connection information to connect to the Master repository and click on Next.

  9. Ensure that the test connection was successful and click on Next.

  10. Check the Managed Servers, Clusters and Machines option and click on Next.

  11. Set the port to 8001, accept the defaults, and click on Next.

  12. Click on the Next button on the Configure Clusters screen.

  13. Accept the defaults and click on Next on the Configure Machines screen.

  14. Click on Next on the Assign Servers to Machines screen.

  15. Review the Configuration Summary screen and click on Create.

  16. Do not click on the Start Admin Server check box. Then, click on Done.

  17. Start the ODI WLS admin server for this domain from the command shell. Execute startweblogic.cmd or startweblogic.sh from Middlware_Home\user_projects\domains\base_doman\bin.

  18. Security has to be set up for the JAVA EE application to have access to the ODI repository. For this access, an entry needs to be created within the credential store that will allow the JAVA EE Agent to authenticate itself and consume the resources that are needed. This user must already be set up in the ODI Security. To do this, we will do the following:

    1. Execute WLST, connect to our running admin server, and add the credential store.

    2. Start WLST from a command shell and change the directory to Middleware_home\odi_home\oracle_common\common\bin.

    3. Execute WLST.

  19. Execute the following command to connect to the running admin server:

    connect ('weblogic','weblogic1','t3://localhost:7001').
    
  20. Execute the following command to add the correct credential store for ODI Supervisor:

    createCred(map="oracle.odi.credmap", key="SUPERVISOR", user="SUPERVISOR", password="SUNOPSIS", desc="ODI SUPERVISOR Crendential")
    

    Note

    During runtime, the JAVA EE Agent will access this credential store to authenticate itself.

    1. Type exit() to close WLST.

    2. Start a command shell and change the directory to the user_projects directory of the Middleware_Home - Middleware_HOME\user_projects\domains\base_domain\bin.

    3. Execute StartManagedWeblogic odi_server1.

  21. Enter weblogic as the username and weblogic1 as the password.

  22. Verify base_domain is in the running mode and that there are no stack trace errors.

  23. Verify connectivity to Java EE Agent through ODI Studio Topology.

How it works...

Oracle has documented the overall process of installation and configuring the ODI JEE Agent. However, installation and deployment are broken into different sections of the documentation. This recipe gives a quick walk-through of the steps needed to easily install the JEE ODI Agent and also the steps necessary to configure the agent on the WebLogic server as well as the updates required within the ODI repository.

There's more...

The ODI JEE Agent can easily take advantage of the enterprise scalability features of Weblogic Server. Setting up clustering with the ODI JEE Agent is straightforward and follows the same setup as above. However, two agents would be created and then clustered. This is outlined in the Fusion Middleware High Availability Guide at http://docs.oracle.com/cd/E14571_01/core.1111/e10106/odi.htm.

There are also many more exciting ODI JEE components that are also easily installed and deployed, including the ODI Console, the ODI Admin Plug-in for the FMW EM Control, and the SDK Web Services, which are outlined at http://docs.oracle.com/cd/E21764_01/core.1111/e16453/configure.htm#autoId9.

 

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...

  1. Start the Oracle Fusion Middleware Web Tier Utilities 11g installer from its installation directory.

  2. In the Install and Configure section, select Install and Configure and click on Next.

  3. In the Configure Components part of the installer, check the Oracle HTTP Server and Oracle Web Cache checkboxes and then click on Next.

  4. 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:

  5. Click on Next to go to the next installer screen.

  6. In the Web Cache Administrator Password screen, enter the password of your choice. In this recipe, we use welcome1 and click on Next.

  7. In the Configure Ports step, select Auto Port Configuration and click on Next.

  8. Finally, click on Install in the Installation Summary screen to start the Oracle Web Tier Utilities installation process.

  9. 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.

  10. 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
  11. 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).

  12. Then run the following command to start OPMN:

    opmnctl.bat start
    
  13. 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
    
  14. 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
    
  15. 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.

 

Deploying JDBC drivers with a JEE ODI Agent


In this recipe, we will be looking at how to deploy additional JDBC drivers to a JEE ODI Agent. ODI is delivered out of the box with support for many relational databases, however there are times when additional data sources may be needed. The ODI generic knowledge modules support virtually any Type 4 JDBC drivers. If the new source does have a Type 4 JDBC driver, this driver can be used to extract data from the source and can also be used to load to a target.

In this recipe, we will use a JDBC driver that is not shipped out of the box with ODI. Microsoft SQL Server 2012 was released after ODI 11.1.1.6.0 was released. Although there is no specific support listed in the ODI Certification Matrix for ODI 11.1.1.6.0 and SQL Server 2012, the new JDBC driver can be deployed to ODI Studio. Once the driver is deployed to the ODI Studio and the ODI JEE Agent, a SQL Server 2012 data server can be set up in the ODI topology and then used within an integration.

Getting ready

This recipe we will use the Microsoft SQL Server 2012 JDBC driver as an example. This file can be downloaded from Microsoft.com and deployed to ODI as outlined in the following section. No other prerequisites are required.

How to do it…

  1. Download the Microsoft JDBC Driver SQL 4.0 with SQL Server 2012 support from Microsoft.com.

  2. First, make sure to exit all ODI processes on the machine where the driver will be deployed.

  3. Copy the .jar file to the odi\oracledi\userlib path, which is located in the appdate environment variable. For example, on Windows 2008 Server the directory is C:\Users\dsgray\AppData\Roaming\odi\oracledi\userlib\.

  4. Restart ODI Studio, go to topology, and add a new data sever to Microsoft SQL Server Technology.

  5. Set Name, User, and Password to match your SQL 2012 Server settings.

  6. On the JDBC tab, click the search icon to get the list of JDBC drivers. Notice that the new driver now shows in the list Microsoft SQL Server 2005 Driver for JDBC. Select this driver and click on OK.

    Note

    This driver shows SQL Sever 2005, however this is from the description embedded in the driver, not ODI.

  7. Set the JDBC URL appropriately for your SQL 2012 Server.

  8. Click on the Test Connection button.

  9. We have now successfully tested the new driver within ODI Studio. However, we can also test the ODI JEE agent here by selecting OracleDIAgent that was setup in the previous Deploying a JEE ODI Agent recipe.

  10. Click on the Test Connection button.

    Note

    Notice there was an error during this test. This was expected as the ODI JEE Agent could not load the appropriate class since the SQL Server 2012 JDBC .jar file has not been copied to the WebLogic server.

  11. Before copying the SQLServer2012 JDBC .jar file to the Weblogic server, the ODI domain needs to be shut down. Shut down the ODI JEE Agent base domain.

  12. Once the domain is completely shut down, copy the SQLServer2012 JDBC jar to the Middleware_Home\user_projects\domains\base_domain\lib path.

    1. Restart the ODI JEE Agent domain, base domain.

    2. Within ODI Studio, click on the Test Connection button again.

  13. The new JDBC driver has now been successfully set up on the ODI JEE Agent.

There's more...

This recipe demonstrated adding a new relational JDBC driver to the ODI JEE Agent. JDBC drivers do exist for non-relational targets and sources such as Microsoft Excel, or also for applications such as SalesForce.com. Setting up the additional JDBC drivers for these types of technologies would follow the same steps as outlined for the SQL Server 2012 driver.

Latest Reviews (1 reviews total)
Oracle Data Integrator 11g Cookbook
Unlock this book and the full library FREE for 7 days
Start now