Saturday, August 30, 2014

Interface with Union, Minus Operator using ODI SDK

Keeping the Java series, here is more one Java Code for ODI.
The below codes is used to create an Interface that can create Multiple Dataset depending on the source
datastore and Operator provided.
For example – Here
String[] source_datastore={“REGIONS”,”REGIONS”,”REGIONS”};
we are using three different source tables ,you can change them and provide any number of Source datastore in the array and accordingly n number of the Dataset are created.
String[] operator={“UNION”,”MINUS”};
For n number of the source database , n-1 number of the Operator needs to be provided.
For example if there are 4 different source datastore , then 3 operator need to provided which can be same or different depending on your requirement.
package odi.sdk;
import java.util.Collection;
import java.util.Iterator;
import oracle.odi.core.OdiInstance;
import oracle.odi.core.config.MasterRepositoryDbInfo;
import oracle.odi.core.config.OdiInstanceConfig;
import oracle.odi.core.config.PoolingAttributes;
import oracle.odi.core.config.WorkRepositoryDbInfo;
import oracle.odi.core.exception.OdiRuntimeException;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.OdiCKM;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiLKM;
import oracle.odi.domain.project.ProcedureOption;
import oracle.odi.domain.project.finder.IOdiCKMFinder;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiLKMFinder;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.project.interfaces.SourceDataStore;
import oracle.odi.domain.project.interfaces.SourceSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;

public class PermanentInterfaceWithUnion {
private static String Project_Code;
private static String Folder_Name;
private static OdiFolder folder;
private static String Context_Code;
private static OdiContext context;
private static OdiDataStore sourceDatastore;
private static String target_model_name;
private static String source_model_name;
private static String LKM;
private static String IKM;
private static String CKM;
private static String target_datastore;
private static SourceSet srcset;
private static SourceDataStore sd;
private static DataSet dataset;
* @param args
public static void main(String[] args) {
/****** Please change these Parameters *********/
String Url = "jdbc:oracle:thin:@localhost:1521:xe";
String Driver="oracle.jdbc.OracleDriver";
String Master_User="ODI_MASTER_11G";
String Master_Pass="ODI_MASTER_11G";
String WorkRep="WORKREP1";
String Odi_User="SUPERVISOR";
String Odi_Pass="SUNOPSIS";
source_model_name = "SRCE_HR";
String[] source_datastore={"REGIONS","REGIONS","REGIONS"};
String[] operator={"UNION","MINUS"};
target_model_name = "TRGT_HR";
LKM ="LKM SQL to Oracle";
IKM ="IKM SQL Control Append";
CKM ="CKM Oracle";
// Connection

MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(Url, Driver, Master_User,Master_Pass.toCharArray(), new PoolingAttributes());
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(WorkRep, new PoolingAttributes());
OdiInstance odiInstance=OdiInstance.createInstance(new OdiInstanceConfig(masterInfo,workInfo));
Authentication auth = odiInstance.getSecurityManager().createAuthentication(Odi_User,Odi_Pass.toCharArray());
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());
// Find the folder
Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
folder = (OdiFolder);
// Find the Context
context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(Context_Code);
OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiDataStore.class)).findByName(target_datastore, target_model_name);
System.out.println("Interface Creation Started for ..."+ target_datastore);
// Creating a New Interface
OdiInterface intf = new OdiInterface(folder, target_datastore, context);
// Setting the above Context as the Optimization Context
// Reading the Source Data Store
// Find the Data store using the IOdiDataStoreFinder
int order=0;
for (int i=0 ;i<source_datastore.length ;i++) {
sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
findByName(source_datastore[i], source_model_name);
// Creating DataSet to automatically assign different
// Source Data store
// DataSet(OdiInterface pInterface, java.lang.String pName)
if (i == 0) {
dataset = intf.getDataSets().iterator().next();
} else {
dataset=new DataSet(intf,"DATASET"+i);
srcset = new SourceSet("SrcSet01",dataset);
sd=new SourceDataStore(dataset,false,sourceDatastore.getName().toString(),0,sourceDatastore);
// Helper is to manipulate Odi interfaces in an
// interactive way
InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions
(intf, odiInstance, odiInstance.getTransactionalEntityManager());
helper.performAction(new InterfaceActionSetTargetDataStore(
targetDatastore, new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()));
helper.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());
// Add the Filter
//helper.performAction(new InterfaceActionAddFilter(dataset, sd.getName(),ExecutionLocation.WORK));
// Start mapping the KM
// LKM
Collection<OdiLKM> lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(
for (Iterator<OdiLKM> iterator = lkm1.iterator(); iterator.hasNext();) {
OdiLKM odiLKM = (OdiLKM);
helper.performAction(new InterfaceActionSetKM(odiLKM,srcset, KMType.LKM,new KMOptionRetainerHomonymy()));
// Fetching each option of the LKM
for (ProcedureOption c : odiLKM.getOptions()) {
helper.performAction(new InterfaceActionSetKMOptionValue(srcset, KMType.LKM,"DELETE_TEMPORARY_INDEXES", true));
// IKM
// Find the IKM using the IOdiIKMFinder
Collection<OdiIKM> ikm1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(
for (Iterator<OdiIKM> iterator = ikm1.iterator(); iterator.hasNext();) {
OdiIKM odiIKM = (OdiIKM);
// Setting the IKM in the interface
helper.performAction(new InterfaceActionSetKM(odiIKM, intf.getTargetDataStore(),KMType.IKM, new KMOptionRetainerLazy()));
// Fetching each option of the IKM
for (ProcedureOption c : odiIKM.getOptions()) {
// Modifying the Options of the IKM in the
// Interface
helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
"FLOW_CONTROL", false));
helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
// CKM
Collection<OdiCKM> ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
for (Iterator<OdiCKM> iterator = ckm1.iterator(); iterator.hasNext();) {
OdiCKM odiCKM = (OdiCKM);
helper.performAction(new InterfaceActionSetKM(odiCKM, intf.getTargetDataStore(),KMType.CKM, new KMOptionRetainerLazy()));
// Compute the Interface sourceset
try {helper.computeSourceSets();}
catch (InexistentMappingException e) {throw new OdiRuntimeException(e);}
// Persisting the Interface
// Called to inform the ODI persistence layer that this
// interface will be persisted
try {helper.preparePersist();}
catch (oracle.odi.interfaces.interactive.exceptions.OdiInterfaceNotReadyForPersistException e) {e.printStackTrace();}
/*// Generating Scenarios
System.out.println("Generating Scenario for .."+ target_datastore );
IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
OdiScenario newScen = gene.generateScenario(intf,target_datastore, "001");
// Finally close the Instance
System.out.println("Process Completed");

ODI Certification Exam syllabus

Exam: 1Z1-482-Oracle Data Integrator 11g Certified Implementation Essentials-ENU
The Exam tests you on below Subject Areas and OPN Members can access the relevant Online Courses for these as well from the Oracle OPN Site:
1.      Introduction to ODI
2.      ODI Architecture
3.      ODI Models and Data stores
4.      ODI Project Development
5.      ODI Managing Execution
6.      Oracle Golden Gate with ODI
7.      Doing more with ODI
8.      Enterprise Data Quality with ODI
The Exam has a total of 124 questions and last for a duration of 150 Minutes. The Weightage is as below:
1.      ODI Model, Architecture, KM and Development – 50%
2.      ODI CDC and JKM  - 8%
3.      ODI Load Plans and Execution – 10 %
4.      ODI SDK and ODI Console – 5%
5.      EDQ and ODI– 25%
6.      Golden Gate and ODI – 2%
Also some key areas where repeated questions are coming from are as below:
1.      ODI Model, Architecture, KM and Development
a.       Dependency between physical and logical Entities
b.      Use of Security UI in ODI
c.       How Group By Clause works in Interface
d.      Mapping using Data sets
2.      ODI CDC and JKM 
a.       Naming conventions of Views and Tables created by JKM
3.      ODI Load Plans and Execution
a.       Types of steps
b.      Error Handling
c.       Rerun options
4.      ODI SDK and ODI Console
a.       Can we do Admin tasks with ODI SDK
b.      What can we do from the ODI Console
5.      EDQ and ODI
a.       Types of Parsers
b.      Address Validation related Questions
c.       How does EDQ get invoked from ODI
6.      Golden Gate and ODI
a.       User Parameters to configure OGG in ODI

Friday, August 29, 2014

11g Oracle Data Integrator – Upgrading from 10g – Part 4/11g

Following our 11g post series, a very important step is upgrade from 1og. Here is our thoughts.
The post tries to cover the method and issues related to it . I am sure this will give our viewers best idea and steps on how to move forward to newer version.
ODI 11g have an UPGRADE ASSISTANT that will help to upgrade the repository from ODI 10g to ODI 11g.
In case if you trying to upgrade by logging into the Master Repository and upgrade you will encounter the following error.
There is a Upgrade Assistant available in  /bin folder .  When I clicked it directly nothing happen so I knew , I need to call it using command prompt.
So I called the ua.bat being in windows  and after a while an error . oops !! . hmm no idea what it is .?
I went to that path and saw that since I was using the Generic Version so I dint see any WIN 32 bit and saw only WIN64 , although I had OraInstaller.dll.  I thought to myself is it a bug or can we upgrade only with 32 bit ODI version . I am sure that should not be the case , so I started looking at the certification (’ODI ) and I found that for windows its only 32 bit which is supported.
To move on I changed the win32 to win64 and saved ua.bat  I again got the error Smile , I thought let me go for win32 ODI installation .
Moved to 32 bit ODI Installation
Launched the Upgrade Assistant from <ODI_HOME>/bin/ua.bat
I am presently upgrading only the ODI repositories and not other component.
The third options  – Check that Upgrade Occurs only to Cloned Repositories , caught my attention. Still I just clicked Next and moved ahead.
In this screen I checked Database Backup completed , so I thought to myself , looks like I need to do something or I am missing something , so  I searched for the Upgrade from ODI 10g to ODI 11g documentation and I found this
Here they have mentioned to do the cloning or backup of the Repository of Master and Work under the Master . Rather than creating the scripts I used a simple process through SQL Developer –> Database Copy
Before doing so Create the required ODI 11g Master and Work Repository schema and assign the required tablespaces to them .Once done using the Database copy , copy the Tables and the data.
In Next step , SQL Developer lists all the steps  and finally click Finish to start the copy Process. Once completed , you will able to find all the Master Repository tables created in the next Master Repository schema with the required data in it.
Keep repeating the Process for all the Work Repository under that above Master Repository. Once completed Click Next in the Upgrade Assistant to move to Step 4.
Here in Step 4 list all the details required and provide the Master Repository  just created above and provide the SUPERVISOR username and Password used in ODI 10g Repository.
Once the above screen is successful , Upgrade Assistant will lists all the Work Repository under the Master . Now provide the username and password of the schema of Cloned ODI 11g schema .
[ Note in case , any of the above mentioned steps is wrong or incorrect you will get an error screen as shows below
Use the First  or  the Third option  but never the second option as there might be chances of corruption causing issues or improper Repository setup  ]
Once Work Repository schema authentication is done , Click Upgrade to start the Upgradation Process.
Finally the required tables , and LKM are upgraded and the logs can be visited by click on the link mentioned in the Upgrade Assistant .
Now login into the ODI Studio and provide the Master Repository detail and select the appropriate Work Repository and log in , you don’t need to provide the internal ID since it will use the Internal Id specified during ODI 10g setup .
As you can see all the Interface, Model , Topology, agents are all created but you would need to create the appropriate Standalone Agent script for the migrated Agent.
I wanted to test the migration so I executed an Interface and it worked.
I also had another EWR setup under my Master Repository , I logged inside it too.
I can see all the required Scenarios, topology and other ODI objects.
This are some of the simple Upgrade process from ODI 10g  DWR and EWR  to ODI 11g .
Hope to hear from you all how was your experience with the Upgrade Assistant and what kind of errors you encountered .
Keep looking into odiexperts for more ODI 11g series Posts.

11g Oracle Data Integrator – Standalone Agent but, and about the Windows Service

In ODI 11g when I was doing the installation , I got a setup screen to setup the agent  and I created the agent called MAIN_AGENT and I initially thought it’s a cool feature and thought that the Agent was created and automatically the ODIPARAMS and other files will get update .
Once the installation is done and the ODI 11g instance is started , I just triggered the agent and I got an Error . Oops I have not started the agent_MAIN_AGENT.bat and when I started it suddenly vanished , I had a feeling that some thing was wrong  and something is not setup properly, so I went inside the agent_MAIN_AGENT.bat script and saw this
D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=MAIN_AGENT” “-PORT=20910″
In short its just the file calling the agent.bat with the required parameters populated , so started digging around and looked at the odiparams .
The odiparams file was located where the standalone agent is located namely
I looked at it and saw that its pretty much the same but rather than the HSQL driver I was able to see the parameters for oracle. I started updating and provided the required parameters, at the ODI_MASTER_ENCODED_PASS I need to supply the encoded password , so I started typing in the command prompt
agent encode <password>
and ODI was trying to start the agent <password> ,so I knew some thing is changed again and I looked at the folder and I was able to see the encode.bat . Ah ! new funda . so I tried
encode <password >
Voila ! it worked Smile
After updating the required parameters , I was scrolling and looking at the other option , I saw new options  ODI_CONNECTION_RETRY_COUNT , ODI_CONNECTION_RETRY_DELAY
and also the ODI_JAVA_HOME is set with the required jdk path  which mean it no more reads from the JAVA_HOME or setting the environment variable ODI_JAVA_HOME. hmm !
Coming back to the above two option I was trying to find out what do they mean and the definition was defined as follows
I thought ok , let me play with these option some time later and let me restart the agent.
So I went to topology and created and updated the physical and logical agents as required .
Now I thought let me start the agent_MAIN_AGENT.bat and start again .
It  started . Cool ,but then I thought how about if I want to set up again another standalone agent on 20911, so I went to Topology created the Physical and Logical and called it second agent.
After doing so I made the duplicate of the agent_MAIN_AGENT.bat and called it agent_second_agent.bat  and update the required parameters
D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=second_agent” “-PORT=20911″
Now I thought let me start that too and slowly the second_agent started too .
Now I wanted to make them as service so I was looking for agentservice.bat and I looked into the documentation and search for agent service but in vain .I even tried to bring in the Wrapper codes from ODI 10 g into 11g and make it as a service , i was successful in making the service but the conf files calls few java classes which could not be migrated since that would be complicated process.
Looks like we can only use the agentscheduler mode in windows but I can’t believe it… I will try to find out how to start it as service and publish as soon as I discovery!