Executing Interfaces and Procedures in parallel in a Package with ODI
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
I have been working on Oracle Data Integrator (ODI) over the last three weeks. It is an extremely flexible and innovative tool. Claims by Oracle that it is up to ten times faster to develop ETL routines in ODI as compared to traditional ETL tools are actually no exaggeration.
In this article I will show you how to execute Procedures and Interfaces in parallel in a Package. I spent about half a day in the forums etc. before I actually figured out how to set this up. The way you execute Mappings or Transformations in OWB sent me in the wrong direction. While in OWB you use a Fork and AND Operator to accomplish this, in ODI you create a Scenario from your Interfaces and/or Procedures, and connect these inside a Package. Then you just set the Snychronous/Asynchronous parameter in those Scenarios that you want to execute in parallel to Asynchronous Mode.
We’ll now go through each of the steps. We will first create three tables in the SH sample schema. We will then create three Procedures that will load these tables. Finally we will create a Package from the three Procedures that will load two of them in parallel.
Instead of the Procedures we could have used Interfaces. It is the same principle. However, it is easier and quicker to set up and demonstrate the parallel load with Procedures.
[big_data_promotion]
Let’s create the three tables first. Log in to the SH schema via SQL+ and execute the following SQL.
|
1 2 3 4 5 6 |
SQL> create table a (a number); Table created. SQL> create table b (b number); Table created. SQL> create table c (c number); Table created. |
Next we create the Procedures.
I have already created all of the Topologies, Models, Contexts etc. that are pre-requisites for creating the Procedures. Have a look at John Goodwin’s excellent series of articles on ODI for some tutorials.
Log on to Designer. Expand your Project and Folder, right click Procedures and select Insert Procedure.
Populate fields at the Definition tab as per screenshot below.
Name: insert a
Target Technology: Oracle
Change to the Details tab and click on the Add button
Populate the fields as per screenshot below
Name: insert a
Log Counter: Insert
Schema: ORCL_SH. This could be different in your environment. Depending on the name given to the schema in the Topology Manager.
Command: insert into a values (7).
In this Procedure we populate the table a in the SH schema with the value 7.
Next we create the Procedure that populates table b. Go through the same steps as for table a. As the Command type in: insert into b select prod_id from sales.
Next we create the Procedure that populates table c. Go through the same steps as for table a and b. Populate the Command with: insert into c values (8).
Next we need to create a Scenario for each of our Procedures.
Right click the insert a Procedure and from the menu select Generate Scenario…
Leave the default values and click OK.
Repeat the same steps for the other two Procedures.
Next we create the Package. Right click Packages and select Insert Package from the menu.
Populate the Name field as per screenshot
Change to the Diagram tab and drag the following items onto the diagram.
Scenario_A Version 001
Scenario_B Version 001
Scenario_C Version 001
ODIWaitForChildSession tool from the Event Detection toolbox
Next right click the Execution of the Scenario Insert_A item and select First Step from the menu
Next connect the items as per screenshot below
We want to execute Scenario Insert_B and Insert_A in parallel, so we need to set the Synchronous/Asynchronous parameter to Asynchronous Mode for these Scenarios.
Click on the Execution of the Scenario Insert_B. In the Properties area set the Synchronous/Asynchronous parameter to Asynchronous Mode
Do the same for Execution of the Scenario Insert_C.
That’s it. You can now execute the Package and then verify in the Operator that Scenario Insert_B and Insert_C actually executed in parallel.
[big_data_promotion]
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
