ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

November 16, 2011
by kdevendr
1 Comment

Categories: How to , ODI , SDK , Tips and Tricks

Single Post View


Creating Permanent Interface based on Model Level

This particular ODI SDK codes creates one source to one target   interface and accordingly generate Scenario,  based on condition where the Source table is same as Target Table name , under two different Model.

By default the target datastore name is used as the Interface Name.

For this example , Source Model is HR schema and also the Target Model is HR schema.

package odi_sdk;

import java.util.Collection;
import java.util.HashSet;
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.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
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.runtime.scenario.OdiScenario;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.generation.IOdiScenarioGenerator;
import oracle.odi.generation.support.OdiScenarioGeneratorImpl;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKMOptionValue;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerHomonymy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy;
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy;
import oracle.odi.interfaces.interactive.support.sourceset.creators.InexistentMappingException;
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey;

public class PermanentInterface {

	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 OdiInterface odiInterface;
	private static String target_model_name;
	private static String source_model_name;
	private static String LKM;
	private static String IKM;
	private static String CKM;

	/**
	 * @param args
	 */
	public static void main(String[] args) {

        /****** Please change these Parameters *********/

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";

                Project_Code="XMT";
		Context_Code="XMT";
		Folder_Name="FOLDER";
		source_model_name = "SRCE_HR";
		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());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

		// Find the folder

		Collection fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
		for (Iterator it = fold.iterator(); it.hasNext();) {
			folder = (OdiFolder) it.next();
		}

		// Find the Context
		context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(Context_Code);

		// Target Data Store
		Object[] trgt_ds = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findAll().toArray();

		// This part of the code is to remove the
		// duplicate datastores present in different Models
		HashSet hs = new HashSet();
		for (Object dt_str : trgt_ds) {
			OdiDataStore ds = (OdiDataStore) dt_str;
			hs.add(ds.getName().toString());
		}

		for (Object ds : hs) {
			// Target Data Store
			OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().
                        getFinder(OdiDataStore.class)).
                        findByName(ds.toString(), target_model_name);

			if (targetDatastore != null) {
			// Reading the Source Data Store
			// Find the Data store using the IOdiDataStoreFinder
			sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
                        findByName(ds.toString(), source_model_name);

			System.out.println("Interface Creation Started for ..."+ ds.toString());
			// Creating a New Interface
			OdiInterface intf = new OdiInterface(folder, ds.toString(), context);

			// Setting the above Context as the Optimization Context
			intf.setOptimizationContext(context);

			// Creating DataSet to automatically assign different
			// Source Data store
			// DataSet(OdiInterface pInterface, java.lang.String pName)

			DataSet dataset = intf.getDataSets().iterator().next();
			SourceSet srcset = new SourceSet("srcset0",dataset);
			SourceDataStore sd=new SourceDataStore(dataset,false,sourceDatastore.getName().
                        toString(),0,sourceDatastore);
			srcset.addSourceDataStore(sd);

			// 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 lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).
                        findByName(LKM,Project_Code);

			for (Iterator iterator = lkm1.iterator(); iterator.hasNext();) {
				OdiLKM odiLKM = (OdiLKM) iterator.next();
				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", false));
				}
			}

			// IKM
			// Find the IKM using the IOdiIKMFinder

			Collection ikm1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(
							IKM,Project_Code);

			for (Iterator iterator = ikm1.iterator(); iterator.hasNext();) {
				OdiIKM odiIKM = (OdiIKM) iterator.next();

				// 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,
							"STATIC_CONTROL", true));

				}
			}

			// CKM
			Collection ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
					CKM,Project_Code);

			for (Iterator iterator = ckm1.iterator(); iterator.hasNext();) {
				OdiCKM odiCKM = (OdiCKM) iterator.next();
				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();}

			odiInstance.getTransactionalEntityManager().persist(intf);

			// Generating Scenarios

			System.out.println("Generating Scenario for .."+ ds.toString() );
			IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
			OdiScenario newScen = gene.generateScenario(intf,ds.toString(), "001");
			odiInstance.getTransactionalEntityManager().persist(newScen);
			} }

		// Finally close the Instance
		odiInstance.getTransactionManager().commit(trans);
		odiInstance.close();

		System.out.println("Process Completed");
	}

}

Sample Output

November 15, 2011
by kdevendr
2 Comments

Categories: How to , ODI , SDK , Tips and Tricks

Single Post View


Automatically add Interfaces/Scenarios to new ODIPackage using ODI SDK

The below codes  automatically adds all the interface/Scenarios of a Folder  into a New Package .

The below codes are written in such a way so that minimal changes are required .

All you would need to change is the parameters .  Also in the Scenarios we have mentioned only Scenario name and version as -1 so please change accordingly or add more parameters according to your requirement.

Add all the interface of the Folder into Package

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.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiPackage;
import oracle.odi.domain.project.StepInterface;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiInterfaceFinder;

public class AddIntfNewPackage {

	private static String Project_Code;
	private static String Folder_Name;
	private static OdiInterface odiInterface;
	private static StepInterface step_prevscen;
	private static OdiPackage pkg;
    private static StepInterface step_intf;
	private static OdiFolder folder;
	private static String Package_Name;
	/**
	 * @param args
	 */
	public static void main(String[] args) {


		/****** Please change these Parameters *********/

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";

		Project_Code="XMT";
		Folder_Name ="FOLDER";
		Package_Name="";  //Provide Package Name

		/********************************************/

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());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
 ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

 Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance
	                 .getTransactionalEntityManager().getFinder(OdiFolder.class)).
                         findByName(Folder_Name,Project_Code);

	 for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();)
                {
	             folder = (OdiFolder) it.next();
	        }

	     // New Package
	      pkg = new OdiPackage(folder, Package_Name);

	      int i=0;

 Collection<OdiInterface> intf_find = ((IOdiInterfaceFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiInterface.class)).
findByProject(Project_Code, Folder_Name);

	  for (Iterator<OdiInterface> iterator = intf_find.iterator(); iterator.hasNext();)
                   {
		     odiInterface = (OdiInterface) iterator.next();
		     System.out.println("Interface Name is " + odiInterface.getName());

		    step_intf = new StepInterface(pkg,odiInterface,odiInterface.getName());

		     if (i==0) {
		    step_prevscen = new StepInterface(pkg,odiInterface,odiInterface.getName());
		    pkg.setFirstStep(step_prevscen);
		    pkg.removeStep(step_intf);
		   	   i+=1;
		   	   }else{
		   		step_prevscen.setNextStepAfterSuccess(step_intf);
		   		step_prevscen = step_intf;
		   	   }}

               // Persisting the Package
	      odiInstance.getTransactionalEntityManager().persist(pkg);
	      odiInstance.getTransactionManager().commit(trans);
	      odiInstance.close();
	      System.out.println("Process Completed");
	}
}

Add all the scenario of the Folder into the Package.

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.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiPackage;
import oracle.odi.domain.project.StepOdiCommand;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiInterfaceFinder;
import oracle.odi.domain.runtime.scenario.OdiScenario;
import oracle.odi.domain.runtime.scenario.finder.IOdiScenarioFinder;
import oracle.odi.domain.xrefs.expression.Expression;

public class AddScenNewPackage {

	private static String Project_Code;
	private static String Folder_Name;
	private static OdiInterface odiInterface;
	private static StepOdiCommand step_prevscen;
	private static OdiPackage pkg;
    private static StepOdiCommand step_scen;
	private static OdiFolder folder;
	private static String Package_Name;
	/**
	 * @param args
	 */
	public static void main(String[] args) {

		/****** Please change these Parameters *********/

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";

		Project_Code="XMT";
		Folder_Name ="FOLDER";
		Package_Name="TESTING";  //Provide Package Name

		/********************************************/

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());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

	      Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance
	                 .getTransactionalEntityManager().getFinder(OdiFolder.class)).
                          findByName(Folder_Name,Project_Code);

			 for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
	             folder = (OdiFolder) it.next();
	        }

	     // New Package
		 pkg = new OdiPackage(folder, Package_Name);

	      int i=0;

		   Collection<OdiInterface> intf_find = ((IOdiInterfaceFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiInterface.class)).
                   findByProject(Project_Code, Folder_Name);

		   for (Iterator<OdiInterface> iterator = intf_find.iterator(); iterator.hasNext();) {
		     odiInterface = (OdiInterface) iterator.next();

		     Collection<OdiScenario> scen = ((IOdiScenarioFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiScenario.class)).
                     findBySourceInterface(odiInterface.getInterfaceId());

		     for (Iterator<OdiScenario> iterator2 = scen.iterator(); iterator2.hasNext();)
                           {
		   	   OdiScenario odiScenario = (OdiScenario) iterator2.next();
		   	   	System.out.println(odiScenario.getName());
			   	   step_scen = new StepOdiCommand(pkg,odiScenario.getName().toUpperCase());

			   	   step_scen.setCommandExpression(new Expression("OdiStartScen -SCEN_NAME="+odiScenario.getName()+" -SCEN_VERSION=-1",
			   						   null, Expression.SqlGroupType.NONE));
		   	    System.out.println(i);
			   	 if (i==0) {
				   	   step_prevscen = step_scen;
				   	   pkg.setFirstStep(step_prevscen);
				   	   step_prevscen = step_scen;
				   	   i+=1;
				   	   }else{
				   			step_prevscen.setNextStepAfterSuccess(step_scen);
				   			step_prevscen = step_scen;
				   	   }}
		   }

              odiInstance.getTransactionalEntityManager().persist(pkg);
	      odiInstance.getTransactionManager().commit(trans);
	      odiInstance.close();
	      System.out.println("Process Completed");
	}
}

November 15, 2011
by kdevendr
1 Comment

Categories: Logic , ODI , Others , SDK

Single Post View


Creating Package using SDK

This post is about on how to Create Package and call Variable in different mode namely Declare, Set, Refresh and Evaluate Mode and Interface and Scenarios inside the Package ,using ODI .

As you know that in Package we define each ODI Object as Step. Similarly in SDK codes too we declare Step depending on the type of object and call them in the package . For example StepVariable, StepOdiCommand,StepInterface etc.

StepModel
StepOdiCommand
StepOsCommand
StepProcedure
StepSubModel
StepType
StepVariable

Lets take a look at an example of Creating an example in Declare Mode

StepVariable stepDecVar = new StepVariable(pkg,var,StepName);
DeclareVariable DecVar  = new StepVariable.DeclareVariable(stepDecVar);
stepDecVar.setAction(DecVar);

First step is to define the step of Variable Type .

StepVariable stepDecVar = new StepVariable(PACKAGE ,VARIABLE  ,StepName);

Next step is to have the variable in the mode we want the variable to be such as

( DeclareVariable, SetVariable,RefreshVariable etc ).

DeclareVariable DecVar  = new StepVariable.DeclareVariable(StepVariable  NAME);

Next step is setting the action of the step

stepDecVar.setAction(DecVar);

For other types of the Variable Mode you can look at the below codes.

You can call Scenario using the StepOdiCommand

StepOdiCommand odicmnd = new StepOdiCommand(pkg,StepName);
odicmnd.setCommandExpression(new Expression(
"OdiStartScen -SCEN_NAME=SCEN -SCEN_VERSION=001",null, Expression.SqlGroupType.NONE));

Just provide the right expression in the set command expression .

The below codes creates the Package as shows in the below image.

image

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.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiPackage;
import oracle.odi.domain.project.OdiVariable;
import oracle.odi.domain.project.StepInterface;
import oracle.odi.domain.project.StepOdiCommand;
import oracle.odi.domain.project.StepVariable;
import oracle.odi.domain.project.StepVariable.DeclareVariable;
import oracle.odi.domain.project.StepVariable.EvaluateVariable;
import oracle.odi.domain.project.StepVariable.RefreshVariable;
import oracle.odi.domain.project.StepVariable.SetVariable;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiInterfaceFinder;
import oracle.odi.domain.project.finder.IOdiVariableFinder;
import oracle.odi.domain.xrefs.expression.Expression;

public class CreatingPackage {

    private static String Project_Code;
    private static String Folder_Name;
    private static String Package_Name;
    private static OdiFolder folder;
    /**
     * @param args
     */
    public static void main(String[] args) {

               /****** Please change these Parameters *********/

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";

		Project_Code="XMT";
		Folder_Name ="FOLDER";
		Package_Name="PKG_TEST";

// 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());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

         Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance
                 .getTransactionalEntityManager().getFinder(OdiFolder.class)).
                 findByName(Folder_Name,Project_Code);

         for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
             folder = (OdiFolder) it.next();
        }

        OdiPackage pkg = new OdiPackage(folder, Package_Name);
        // Here are some of the examples based on variable using the methods defined below
        //Declare Example
        OdiVariable var1=getProjVariable(odiInstance,"TEST_VARIABLE1",Project_Code);
        System.out.println(var1.getName());
        StepVariable step1=getDeclareVariable(pkg, var1, var1.getName());

        //Set Example
        OdiVariable var2=getProjVariable(odiInstance,"TEST_VARIABLE1",Project_Code);
        StepVariable step2=getSetVariable(pkg, var2, var2.getName(), "15");

        //Refresh Example
        OdiVariable var3=getProjVariable(odiInstance,"TEST_VARIABLE1",Project_Code);
        StepVariable step3=getRefreshVariable(pkg, var3, var3.getName());

        //Evaluate Example
        OdiVariable var4=getProjVariable(odiInstance, "TEST_VARIABLE1",Project_Code);
        StepVariable step4=getEvaluateVariable(pkg, var4, var4.getName(), "=", "Y");

        // Refresh Variable Failure
        OdiVariable var5=getProjVariable(odiInstance, "TEST_VARIABLE2",Project_Code);
        StepVariable step5=getRefreshVariable(pkg, var5, var5.getName());

        // Scenario
        StepOdiCommand scen1=getOdiCmnd(pkg, "SCEN",
                 "OdiStartScen -SCEN_NAME=SCEN -SCEN_VERSION=001");

        // Interface
        StepInterface intf = getIntf(odiInstance, pkg, "TEST_INTF", "TEST_INTF");
        // Linking the steps depending on success or Failure .
        // Categorized for easy understanding and link
        //Success
        pkg.setFirstStep(step1);
        step1.setNextStepAfterSuccess(step2);
        step2.setNextStepAfterSuccess(step3);
        step3.setNextStepAfterSuccess(step4);
        step4.setNextStepAfterSuccess(scen1);
        scen1.setNextStepAfterSuccess(intf);

        //Failure
        step1.setNextStepAfterFailure(step5);
        step2.setNextStepAfterFailure(step5);
        step3.setNextStepAfterFailure(step5);
        step4.setNextStepAfterFailure(step5);
        scen1.setNextStepAfterFailure(step5);
        intf.setNextStepAfterFailure(step5);
        // Persisting Package
         odiInstance.getTransactionalEntityManager().persist(pkg);
        /*** Change Ends here **/

        // Finally close the Instance
        odiInstance.getTransactionManager().commit(trans);
        odiInstance.close();

        System.out.println("Completed ");

    }
        /*****************************************************/
        // Please find few methods that can be called any number of times for few types of StepType
        /*****************************************************/
        //Find Global Variable
        public static OdiVariable getGlobalVariable(OdiInstance odiInstance,String GlobalVariable) {
            OdiVariable var=((IOdiVariableFinder) odiInstance.getTransactionalEntityManager().getFinder(
                            OdiVariable.class)).findGlobalByName(GlobalVariable);

            return var;
        }

        //Find Project Variable
        public static OdiVariable getProjVariable(OdiInstance odiInstance,String ProjectVariable,String ProjectCode) {
            OdiVariable var=((IOdiVariableFinder) odiInstance.getTransactionalEntityManager().getFinder(
                            OdiVariable.class)).findByName(ProjectVariable,ProjectCode);

            return var;
        }

        // Declare Variable
        public static StepVariable getDeclareVariable(OdiPackage pkg,OdiVariable var,String StepName) {

            StepVariable stepDecVar = new StepVariable(pkg,var,StepName);
            DeclareVariable DecVar  = new StepVariable.DeclareVariable(stepDecVar);
            stepDecVar.setAction(DecVar);

            return stepDecVar;

        }

        // Refresh Variable
        public static StepVariable getRefreshVariable(OdiPackage pkg,OdiVariable var,String StepName) {

            StepVariable stepRefVar = new StepVariable(pkg,var,StepName);
            RefreshVariable RefVar  = new StepVariable.RefreshVariable(stepRefVar);
            stepRefVar.setAction(RefVar);

            return stepRefVar;
        }

        // Set Variable
        public static StepVariable getSetVariable(OdiPackage pkg,OdiVariable var,String StepName,String Value) {

            StepVariable stepSetVar = new StepVariable(pkg,var,StepName);
            SetVariable SetVar  = new StepVariable.SetVariable(stepSetVar);
            SetVar = new StepVariable.SetVariable(Value);
            stepSetVar.setAction(SetVar);

            return stepSetVar;

        }

        // Evaluate Variable
        public static StepVariable getEvaluateVariable(OdiPackage pkg,OdiVariable var,String StepName,String Operator,String Value) {

            StepVariable stepEvaVar = new StepVariable(pkg,var,StepName);
            EvaluateVariable EvaVar  = new StepVariable.EvaluateVariable(stepEvaVar);
            EvaVar = new  StepVariable.EvaluateVariable(Operator,Value);
            stepEvaVar.setAction(EvaVar);

            return stepEvaVar;

        }

        // ODI Command
        public static StepOdiCommand getOdiCmnd (OdiPackage pkg,String StepName,String Command) {

            StepOdiCommand odicmnd = new StepOdiCommand(pkg,StepName);
            odicmnd.setCommandExpression(new Expression(Command,null, Expression.SqlGroupType.NONE));
            return odicmnd;

        }

        // Interface
        public static StepInterface getIntf (OdiInstance odiinstance,OdiPackage pkg,String Interface,String StepName) {

            StepInterface stepIntf = null;
             Collection<OdiInterface> intf_find = ((IOdiInterfaceFinder) odiinstance.getTransactionalEntityManager().getFinder(OdiInterface.class)).findByName(Interface, Project_Code, Folder_Name);
             for (Iterator<OdiInterface> iterator = intf_find.iterator(); iterator.hasNext();) {
                 OdiInterface    intf = (OdiInterface) iterator.next();
                 stepIntf = new StepInterface(pkg,intf,StepName);

                 }return stepIntf;
        }

}

November 13, 2011
by kdevendr
6 Comments

Categories: How to , Knowledge Modules , ODI , Tips and Tricks

Tags: , , , , ,

Single Post View


Handling the “ORDER BY” clause in an interface

Hello Everyone…

We always get a lot of emails asking about how to “Order By” in ODI because of that, here there is a simple way to do it…

Stays as a suggestion to Oracle implement as default feature in forward versions!

A simple example how to handle Order By in ODI.

Solution 1

Step 1.  Create an Option

image

Step 2. Add the code into KM

<% if (odiRef.getOption("ORDER_BY").equals("1")) { %>
ORDER BY
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD1)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD2)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD3)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD4)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD5)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD6)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD7)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD8)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD9)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD10)")%>
<%} %>

This way you can have the KM to handle with and without Order By option .

Step 3.  Mark the Columns accordingly with UD1

image

Solution 2

Step 1.  Create an Option

image

Step 2 . Add the option into KM

<%=odiRef.getOption("ORDER_BY")%>

Step 3.  Add the Order By Clause accordingly

Provide the Order By clause accordingly for N number of columns  in the interface

image

Sample Output

image.

Good to see you around…

Keep visiting us at www.odiexperts.com

September 26, 2011
by kdevendr
1 Comment

Categories: How to , Logic , ODI , Others , Tips and Tricks

Tags: ,

Single Post View


Creating Temporary Interface using ODI SDK

In this below example we are creating a temporary interface with HR table REGIONS as the source .

Please go through this post ( http://odiexperts.com/odi-sdk-setup-and-config-in-eclipse ) before proceeding and this post is a continuation of the objects created in the old Post.

The below codes assumes that we already have the HR model with REGIONS datastore.

// Find the Project
// Using IFinder find the project , Folder , Context and Logical Schema ,which
// will be used to create  the Temporary Interface.

 project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiProject.class)).
findByCode("XMT");

// Find the folder
// Here among the collection of Folder we are looking for  (FOLDER)
 Collection fold = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiFolder.class)).
findByName("FOLDER");

for (java.util.Iterator it=fold.iterator(); it.hasNext();){
     folder=(OdiFolder)it.next();
}

// Find the Context
   context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiContext.class)).
findByCode("XMT");

//Find the Oracle Logical Schema
   oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

//Importing the KM
// ImportServiceImpl(OdiInstance pOdiInstance)

IImportService importService = new ImportServiceImpl(odiInstance);

try {

// Specify the path of the XML files ok KM .
String KM_PATH = "C:\Oracle\Middleware\Oracle_ODI1\oracledi\xml-reference";

// Import the KMs using duplication mode

// importObjectFromXml(int pImportMode, java.lang.String pFileName,
//    IImportRoot pObjectParent, boolean pDeclareMissingRepository)
// This method imports an object from an OracleDI export file (XML) under a parent object.

importService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, KM_PATH
+ "\KM_IKM SQL Control Append.xml",proj, false);
} catch (OdiImportNotSupportedException e) {

throw new OdiRuntimeException(e);
} catch (OdiImportException e) {throw new OdiRuntimeException(e);
} catch (IOException e) {throw new OdiRuntimeException(e);
}

// Eclipse can help to create the Exception automatically if it
// detects an Exception, so Exception handling can be easy task.

// Creating a New Interface
// OdiInterface(OdiFolder pFolder, java.lang.String pName, OdiContext pOptimizationContext)

OdiInterface intf = new OdiInterface(fold, "INTF_REGIONS",context);

// Setting the above Context as the Optimization Context

intf.setOptimizationContext(context);

// Creating DataSet to automatically assign different Source Data store
// DataSet(OdiInterface pInterface, java.lang.String pName)

// A DataSet is a subset of sources that can be combined with other
// DataSets using Set operators (such as UNION, MINUS, etc.). DataSets contain SourceDataStores, Joins, Filters and
// TargetMappings (which are occurrences of mappings executed on Source or Staging Area.

DataSet dataset = intf.getDataSets().iterator().next();

// Reading the Source Data Store
// Find the Data store using the IOdiDataStoreFinder

OdiDataStore sourceDatastore = ((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName("REGIONS",
"HR");

 // Creating the Target Data Store
// Helper is to manipulate Odi interfaces in an interactive way

// InteractiveInterfaceHelperWithActions(OdiInterface pInterface, OdiInstance pOdiInstance,
// IOdiEntityManager pOdiEntityManager)

InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions(
intf, odiInstance, odiInstance.getTransactionalEntityManager());

helper.performAction(new InterfaceActionAddSourceDataStore(sourceDatastore, dataset,
new AliasComputerDoubleChecker(),new ClauseImporterLazy(),new AutoMappingComputerColumnName()));

// Using the above create Logical Schema in place of Set
// Staging area different from Target

helper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(oracleLogicalSchema));

// Creating a Temporary Table Name so using the Format
// Source Data Store + " TEMP"

helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetName(
sourceDatastore.getName().toString() + "_TEMP"));

// Setting the Schema as Work Schema ( Temporary Schema) where Temporary Table will be created

helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema(
DatabaseSchema.TEMPORARY_SCHEMA));

// Fetching the Source Columns and adding it to the target
// table and performing the automatic Columns Mapping

Object[] col = sourceDatastore.getColumns().toArray();
for (int i = 0; i < col.length; i++) {
try {
     OdiColumn column = (OdiColumn) col[i];
     helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreAddColumn(
     column, new AutoMappingComputerColumnName()));
// Each Source Column is read and added to target table and
// automatically mapped using AutoMappingComputerColumnName

 } catch (UnknownActionException e1) {e1.printStackTrace();}
     }

// Start mapping the KM
// Since both the source and the Temporary Table is in the
// same Data Server so no LKM only IKM

// IKM
// Find the IKM using the IOdiIKMFinder

Collection<OdiIKM> ikm1 = ((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName("IKM SQL Control Append", "XMT");

for (Iterator iterator = ikm1.iterator(); iterator.hasNext();) {
     OdiIKM odiIKM = (OdiIKM) iterator.next();

// Fetching each option of the IKM
for (ProcedureOption c : odiIKM.getOptions()) {
// Setting the IKM in the interface
helper.performAction(new InterfaceActionSetKM(
odiIKM, intf.getTargetDataStore(),
KMType.IKM, new KMOptionRetainerLazy()));

// 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,"TRUNCATE", true));

helper.performAction(new InterfaceActionSetKMOptionValue(
intf.getTargetDataStore(), KMType.IKM,"CREATE_TARG_TABLE", true));

}

}

// Compute the Interface sourceset

try {
helper.computeSourceSets();
//Should be called at some point after source data stores are added to or
// removed from the interface, or some mappings/joins/filters have been added
// or had their locations changed, to create the correct source sets.
// Typically called before setting the KMs for the interface source set, or before calling preparePersist

 } 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();
}

After successful execution the interface is created successfully and with the columns added to target and mapped .

Also the IKM is set with the options specified.

image

image

image

The Complete Java Code

package odi.sdk;

import java.io.IOException;
import java.util.Collection;
import java.util.Iterator;

import oracle.odi.core.OdiInstance;
import oracle.odi.core.exception.OdiRuntimeException;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.TransactionCallbackWithoutResult;
import oracle.odi.core.persistence.transaction.support.TransactionTemplate;
import oracle.odi.domain.model.OdiColumn;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiInterface.DatabaseSchema;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.ProcedureOption;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.project.finder.*;
import oracle.odi.domain.topology.finder.*;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.impexp.IImportService;
import oracle.odi.impexp.OdiImportException;
import oracle.odi.impexp.OdiImportNotSupportedException;
import oracle.odi.impexp.support.ImportServiceImpl;
import oracle.odi.interfaces.interactive.exceptions.UnknownActionException;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreAddColumn;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreSetName;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKMOptionValue;
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerDoubleChecker;
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerColumnName;
import oracle.odi.interfaces.interactive.support.sourceset.creators.InexistentMappingException;
import oracle.odi.publicapi.samples.SimpleOdiInstanceHandle;

public class TempIntf {

	private static OdiProject project;
	private static OdiFolder folder;
	private static OdiLogicalSchema oracleLogicalSchema;
	private static OdiContext context;

	public static void main(String[] args) {

	final SimpleOdiInstanceHandle odiInstanceHandle = SimpleOdiInstanceHandle
	.create("jdbc:oracle:thin:@localhost:1521:orcl",
	"oracle.jdbc.OracleDriver",
	"ODI_MASTER_11G",
	"ODI_MASTER_11G",
	"WORKREP1",
	"SUPERVISOR",
	"SUNOPSIS");

	final OdiInstance odiInstance = odiInstanceHandle.getOdiInstance();
	try {
	TransactionTemplate tx = new TransactionTemplate(odiInstance.getTransactionManager());
	tx.execute(new TransactionCallbackWithoutResult()
	{
	   protected void doInTransactionWithoutResult(ITransactionStatus pStatus)
	   {

		 // Find the Project
		 project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).
findByCode("XMT");

		 // Find the folder
		 Collection fold = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).
findByName("FOLDER");

	        for (java.util.Iterator it=fold.iterator(); it.hasNext();){
	          folder=(OdiFolder)it.next();
	        }
		 // Find the Context
		 context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).
findByCode("XMT");

		 //Find the Oracle Logical Schema
		 oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

		 //Importing the KM

		// ImportServiceImpl(OdiInstance pOdiInstance)

		IImportService importService = new ImportServiceImpl(odiInstance);

		try {

		// Specify the path of the XML files ok KM .
		String KM_PATH = "H:\oracle\ODI_Middleware\Oracle_ODI1\oracledi\xml-reference";

		// Import the KMs using duplication mode

		// importObjectFromXml(int pImportMode, java.lang.String pFileName,
//		    IImportRoot pObjectParent, boolean pDeclareMissingRepository)
		// This method imports an object from an OracleDI export file (XML) under a parent object.

		importService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, KM_PATH
		+ "\KM_IKM SQL Control Append.xml",project, false);
		} catch (OdiImportNotSupportedException e) {

		throw new OdiRuntimeException(e);
		} catch (OdiImportException e) {throw new OdiRuntimeException(e);
		} catch (IOException e) {throw new OdiRuntimeException(e);
		}

		// Eclipse can help to create the Exception automatically if it
		// detects an Exception, so Exception handling can be easy task.

		// Creating a New Interface
		// OdiInterface(OdiFolder pFolder, java.lang.String pName, OdiContext pOptimizationContext)

		OdiInterface intf = new OdiInterface(folder, "INTF_REGIONS",context);

		// Setting the above Context as the Optimization Context

		intf.setOptimizationContext(context);

		// Creating DataSet to automatically assign different Source Data store
		// DataSet(OdiInterface pInterface, java.lang.String pName)

		// A DataSet is a subset of sources that can be combined with other
		// DataSets using Set operators (such as UNION, MINUS, etc.). DataSets contain SourceDataStores, Joins, Filters and
		// TargetMappings (which are occurrences of mappings executed on Source or Staging Area.

		DataSet dataset = intf.getDataSets().iterator().next();

		// Reading the Source Data Store
		// Find the Data store using the IOdiDataStoreFinder

		OdiDataStore sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
findByName("REGIONS","HR");

		 // Creating the Target Data Store
		// Helper is to manipulate Odi interfaces in an interactive way

		// InteractiveInterfaceHelperWithActions(OdiInterface pInterface, OdiInstance pOdiInstance,
		// IOdiEntityManager pOdiEntityManager)

		InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions(
		intf, odiInstance, odiInstance.getTransactionalEntityManager());

		helper.performAction(new InterfaceActionAddSourceDataStore(sourceDatastore, dataset,
		new AliasComputerDoubleChecker(),new ClauseImporterLazy(),new AutoMappingComputerColumnName()));

		// Using the above create Logical Schema in place of Set
		// Staging area different from Target

		helper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(oracleLogicalSchema));

		// Creating a Temporary Table Name so using the Format
		// Source Data Store + " TEMP"

		helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetName(
		sourceDatastore.getName().toString() + "_TEMP"));

		// Setting the Schema as Work Schema ( Temporary Schema) where Temporary Table will be created

		helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema(
		DatabaseSchema.TEMPORARY_SCHEMA));

		// Fetching the Source Columns and adding it to the target
		// table and performing the automatic Columns Mapping

		Object[] col = sourceDatastore.getColumns().toArray();
		for (int i = 0; i < col.length; i++) {
		try {
		     OdiColumn column = (OdiColumn) col[i];
		     helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreAddColumn(
		     column, new AutoMappingComputerColumnName()));
		// Each Source Column is read and added to target table and
		// automatically mapped using AutoMappingComputerColumnName

		 } catch (UnknownActionException e1) {e1.printStackTrace();}
		     }

		// Start mapping the KM
		// Since both the source and the Temporary Table is in the
		// same Data Server so no LKM only IKM

		// IKM
		// Find the IKM using the IOdiIKMFinder

		Collection ikm1 = ((IOdiIKMFinder) odiInstance
		.getTransactionalEntityManager().getFinder(
		OdiIKM.class)).findByName("IKM SQL Control Append", "XMT");

		for (Iterator iterator = ikm1.iterator(); iterator.hasNext();) {
		     OdiIKM odiIKM = (OdiIKM) iterator.next();

		// Fetching each option of the IKM
		for (ProcedureOption c : odiIKM.getOptions()) {
		// Setting the IKM in the interface
		helper.performAction(new InterfaceActionSetKM(
		odiIKM, intf.getTargetDataStore(),
		KMType.IKM, new KMOptionRetainerLazy()));

		// 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,"TRUNCATE", true));

		helper.performAction(new InterfaceActionSetKMOptionValue(
		intf.getTargetDataStore(), KMType.IKM,"CREATE_TARG_TABLE", true));

		}

		}

		// Compute the Interface sourceset

		try {
		helper.computeSourceSets();
		//Should be called at some point after source data stores are added to or
		// removed from the interface, or some mappings/joins/filters have been added
		// or had their locations changed, to create the correct source sets.
		// Typically called before setting the KMs for the interface source set, or before calling preparePersist

		 } 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();
		}

		// Persist the Data Server , Physical and Logical Schema

		   //odiInstance.getTransactionalEntityManager().persist(oracleTechnology);

		   System.out.println("Done");

		   }
		   });
		   }

		   finally

		   {
		   odiInstanceHandle.release();
		   }
}

}

September 18, 2011
by kdevendr
0 comments

Categories: Administration , How to , ODI , Tips and Tricks

Tags: , ,

Single Post View


Creating ODI Procedure using SDK

This post is about to create ODI procedure using the SDK codes.
  • The logic is pretty simple Initially find the Project, Folder , Technology and store them into Variable.
  • Declare a new ODI Procedure
  • Add the Line and for each line define the parameters , technology and expression for Target and Source if required
  • Finally persist the Procedure.
// Find the Context
context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiContext.class)).
findByCode("XMT");

//Find the Oracle Logical Schema
oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

//Find the Oracle Technology
oracleTechnology =((IOdiTechnologyFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiTechnology.class)).
findByCode("ORACLE");

// Creating a New Procedure of the name test_pro under the folder(FOLDER)
OdiUserProcedure pro=new OdiUserProcedure(folder ,"test_pro");
// Creating the first line
 pro.addLine("first_line");

// Declaring the Line Command
OdiProcedureLineCmd cmd =new OdiProcedureLineCmd();
// Add the required properties for the command
cmd.setExecutionContext(context);
cmd.setTechnology(oracleTechnology);
cmd.setLogicalSchema(oracleLogicalSchema);
cmd.setAutoCommitMode();

// defining the experssion and set it to the command
String expr = "Select 1 from dual";
cmd.setExpression(new Expression(expr, null,SqlGroupType.NONE));

//for each line add the required properties from above.
for (OdiUserProcedureLine line: pro.getLines()) {
     System.out.print(line);
     line.setOnTargetCommand(cmd);
     line.setLogLevel(4);
     line.setLogCounter(LogCounter.INSERT);
           }

// Persist(save) the Procedure

odiInstance.getTransactionalEntityManager().persist(pro);

image

image

image

Java Codes

package odi.sdk;

import java.util.Collection;

import oracle.odi.core.OdiInstance;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.TransactionCallbackWithoutResult;
import oracle.odi.core.persistence.transaction.support.TransactionTemplate;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiProcedureLine.LogCounter;
import oracle.odi.domain.project.OdiProcedureLineCmd;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.OdiUserProcedure;
import oracle.odi.domain.project.OdiUserProcedureLine;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.OdiTechnology;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiLogicalSchemaFinder;
import oracle.odi.domain.topology.finder.IOdiTechnologyFinder;
import oracle.odi.domain.xrefs.expression.Expression;
import oracle.odi.domain.xrefs.expression.Expression.SqlGroupType;
import oracle.odi.publicapi.samples.SimpleOdiInstanceHandle;

public class OdiProc {

	private static OdiProject project;
	private static OdiFolder folder;
	private static OdiLogicalSchema oracleLogicalSchema;
	private static OdiContext context;
	private static OdiTechnology oracleTechnology;

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		final SimpleOdiInstanceHandle odiInstanceHandle = SimpleOdiInstanceHandle
		.create("jdbc:oracle:thin:@localhost:1521:orcl",
		"oracle.jdbc.OracleDriver",
		"ODI_MASTER_11G",
		"ODI_MASTER_11G",
		"WORKREP1",
		"SUPERVISOR",
		"SUNOPSIS");

		final OdiInstance odiInstance = odiInstanceHandle.getOdiInstance();
		try {
		TransactionTemplate tx = new TransactionTemplate(odiInstance.getTransactionManager());
		tx.execute(new TransactionCallbackWithoutResult()
		{
		   protected void doInTransactionWithoutResult(ITransactionStatus pStatus)
		   {

			// Find the Project
			project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).
findByCode("XMT");

			// Find the folder
			Collection fold = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).
findByName("FOLDER");

			for (java.util.Iterator it=fold.iterator(); it.hasNext();){
			     folder=(OdiFolder)it.next();
			     }

			// Find the Context
			context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).
findByCode("XMT");

			//Find the Oracle Logical Schema
			oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

			//Find the Oracle Technology
			oracleTechnology =((IOdiTechnologyFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiTechnology.class)).
findByCode("ORACLE");

			OdiUserProcedure pro=new OdiUserProcedure(folder ,"test_pro");
            pro.addLine("first_line");

            OdiProcedureLineCmd cmd =new OdiProcedureLineCmd();
            cmd.setExecutionContext(context);
            cmd.setTechnology(oracleTechnology);
            cmd.setLogicalSchema(oracleLogicalSchema);
            cmd.setAutoCommitMode();

           String expr = "Select 1 from dual";
           cmd.setExpression(new Expression(expr, null,SqlGroupType.NONE));

            for (OdiUserProcedureLine line: pro.getLines()) {
                  System.out.print(line);
                  line.setOnTargetCommand(cmd);
                  line.setLogLevel(4);
                  line.setLogCounter(LogCounter.INSERT);
            }

            odiInstance.getTransactionalEntityManager().persist(pro);

            //Done
			System.out.println("Done");

		   }
		   });
		   }

		   finally

		   {
		   odiInstanceHandle.release();
		   }

	}

}

August 18, 2011
by kdevendr
16 Comments

Categories: How to , Logic , ODI , Others

Tags: , ,

Single Post View


ODI SDK Setup and Config in Eclipse

ODI SDK gives ODI 11g a big edge and a large possibility of automation , smarter coding and cut down in development time . In this post I am going to use Eclipse to configure and setup the the SDK so we can start writing faster and smarter codes. Although there are many other tools to do the job , its just my preference and ease of use which made me use eclipse and write this post.

Setup Eclipse

Weblogic comes with Oracle Enterprise pack for Eclipse else you can Download the Eclipse Classic –http://www.eclipse.org/downloads/

Once Download , Extract the folder and double click the Eclipse.exe and you will be prompted for a Workspace folder. You can use the default or Create a new Folder  use as the workspace.

Workspace is where Eclipse stores all the projects.

image

Now click on the icon as shown in the image and click on the Project .

image

Select Java Project and click Next

image

Provide a Project Name and eclipse would automatically select the default JRE in case it doesn’t you can click on Configure JREs and select the java folder . Finally click Next.

image

Go to the Libraries tab and click on the Add External JAR

image

Add JAR Files

Go and find the folder in oracledi.sdklib and select all the jar files under this folder. Although the main jar is odi-core.jar but it have some link with other jar inside and in order to avoid the confusion  I have just selected all the jar inside the folder .

For my example the directory is – >

C:OracleMiddlewareOracle_ODI1oracledi.sdklib

image

Apart from the above jar files , we also need to include the database jar file where the ODI repository resides. For my example ODI Master and Work repository is on Oracle 11.2 version so ojdbc6.jar file .

image

Finally click the Finish button. Now we are ready to start our coding .

image

Creating First Code

Lets start creating out First piece of Code. Our first piece of code is to create a Project and a Folder.
To do so , Right click on Folder and select New and then Class

image

I have created a package called odi.sdk , you can use the main src or create a separate package which is recommended.

Provide the Class Name and select the option as show in the below image

image

copy paste this standard codes as this code will be the main skeleton for all ODI SDK development after this

public static void main (String [] args ) { << paste the below codes and change the connection credentials >> }

final SimpleOdiInstanceHandle odiInstanceHandle = SimpleOdiInstanceHandle.create
("jdbc:oracle:thin:@localhost:1521:XE",
"oracle.jdbc.OracleDriver",
"ODI_MASTER_11G",
"ODI_MASTER_11G",
"WORKREP1",
"SUPERVISOR",
"SUNOPSIS");

// Allocate an odisinstance of the name
final OdiInstance odiInstance = odiInstanceHandle.getOdiInstance();
try
  {
     TransactionTemplate tx = new TransactionTemplate(odiInstance.getTransactionManager());
     tx.execute(new TransactionCallbackWithoutResult()
        {
         protected void doInTransactionWithoutResult(ITransactionStatus pStatus)
         {

        /*
         *  <<  ODI SDK Codes goes here >>
         */

          } });
     }

finally
        {
         odiInstanceHandle.release();
        }

For ODI 11.1.1.3 and later

        /****** 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";

// 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());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

        /*
         *  <<  ODI SDK Codes goes here >>
         */

// Finally close the Instance
        odiInstance.getTransactionManager().commit(trans);
        odiInstance.close();

image

As you can see some read mark on some of the codes and that is because we have not imported the required java libraries .

Importing library is just a matter of click now. Just place your mouse on the red underlined code . you will see options as shown in the image . Now click on the Import SimpleOdiInstanceHandle. Keep doing so for other underlined codes and eclipse will import automatically .

image

After doing that as you can see that eclipse has neatly and smartly import the required libraries.

image

Now go and change the connection properties and change it accordingly to connect to your Master and Work Repository.

Lets first make sure if we are connected and we can start playing around with more ODI SDK codes.

In order to test the connection and if I am connected I always use this code to test it .

OdiTechnology oracleTechnology =((IOdiTechnologyFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiTechnology.class)).findByCode("ORACLE");

System.out.println(oracleTechnology.getName());

As you can see the output will be like this , which mean the credentials are correct and now we are ready to write more codes.

image

Creating  Project, Folder and Context

Lets start with creating a Project , Folder and Context and setting the Context as Default.

Also you would need this API document in better understanding arguments to be passed.

http://download.oracle.com/docs/cd/E14571_01/apirefs.1111/e17060/toc.htm

OdiProject proj = new OdiProject("XMT", "XMT");
//OdiProject(java.lang.String pName, java.lang.String pCode)

OdiContext context=new OdiContext("XMT");
//OdiContext(java.lang.String pCode)

context.setDefaultContext(true);

Searching the Classes

Lets see how to find the required class .

To search go to the above link (http://download.oracle.com/docs/cd/E14571_01/apirefs.1111/e17060/toc.htm ) and press find depending on your browser and type odiproject and browser will show the required alternative and click the class you were looking for .

As you can see for this example i searched for odiproject and found the class OdiProject and on the right side i can see all the Methods and Constructor and use the same in writing the SDK codes.

The Constructor of the OdiProject is

OdiProject(java.lang.String pName, java.lang.String pCode).

Here i am replacing the Name and code with XMT  and so the codes will be

  • OdiProject proj = new OdiProject(“XMT”, “XMT”);

The Constructor for OdiContext is OdiContext(java.lang.String pCode)

  • OdiContext context=new OdiContext(“XMT”);

I also wanted to see the new context as the default context , so add the method setDefaultContext(boolean);

  •  context.setDefaultContext(true);

Eclipse can help you in writing the arguments .

image

Eclipse will show more method once you press (period) and as show in the below image, but we always need the help of the API so we can pass the correct arguments.

image

image

Lets Execute the code and lets see if the codes have been reflected in the ODI .

image

image

Its blank . hmm so we are missing something . We are missing the Persist .

Persist is like saving the codes into the Repository, so make sure we add the persist as required  .

Lets add the persist  and run it again.

image

As you can see the execution got successful and we can see the Project, Folder and Context  in the ODI Designer.

image image

IFinder

IFinder is used to find different ODI objects according to ODI object and there are different IFinder to find the respective ODI objects

For Ex – IOdiCKMFinder, IOdiColumnFinder, IOdiConditionFinder, IOdiContextFinder, IOdiContextualAgentMappingFinder, IOdiContextualSchemaMappingFinder

Writing the IFinder codes are very easy . Let me share with you the technique i use.

project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class))

Have a default template as shown above and accordingly change the Object type . Lets say if i want to search for OdiFolder

Replace the OdiProjectFinder to OdiFolderFinder and
OdiProject.class to OdiFolder.class and replace the variable project to folder etc.

Lets look at this simple example where I am using the IOdiProjectFinder to list all the project in the work repository.

// List all the projects

Object[] project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiProject.class)).findAll().toArray();

 for ( int i =0 ;i <project.length ; i++ )  {
    OdiProject pro1=(OdiProject) project[i];
   // We need to cast the object project accordingly, for this example OdiProject
 System.out.println("Project Name "+pro1.getName());
}

// (or)

for (Object object : project) {
     OdiProject pro2=(OdiProject) object;
     // Here i am casting the object as OdiProject
     System.out.println("Project Name "+pro2.getName());
}

 //Reading the List of the Folder under a Project
//

 Collection fold1 = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiFolder.class)).findByProject("XMT");

for (Iterator iterator = fold1.iterator(); iterator.hasNext();) {
     OdiFolder object = (OdiFolder) iterator.next();
     System.out.println("Folder Name "+object.getName());
}

image

Eclipse can help to write the for loop too.

To do so

type – for and press ( control + space keys ) and eclipse will show automatically the option and since we are writing for the collection select the required for loop and change the Object to OdiFolder .

image

Creating  Data Server , Physical Schema and Logical Schema .

// Create a Data Server , Physical Schema, Logical Schema

// First lets make sure if we have the Oracle Technology

OdiTechnology oracleTechnology = ((IOdiTechnologyFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiTechnology.class)).findByCode("ORACLE");
// using the IOdiTechnologyFinder to finder the required technology
if (oracleTechnology != null) {
    oracleDataServer = new OdiDataServer(oracleTechnology,"ORCL_TARGET");

// connection settings
//Providing the  Url and Driver
oracleDataServer.setConnectionSettings(new OdiDataServer.JdbcSettings(
"jdbc:oracle:thin:@localhost:1521:orcl",
"oracle.jdbc.driver.OracleDriver"));

// UserName and Password
oracleDataServer.setUsername("HR");
oracleDataServer.setPassword(ObfuscatedString.obfuscate("HR"));
// ObfuscatedString.obfuscate will save the password in the encrypted format
}

// Creating a New Physical Schema
OdiPhysicalSchema oraclePhysicalSchema = new OdiPhysicalSchema(oracleDataServer);

// Set additional information on the schema
oraclePhysicalSchema.setSchemaName("HR");
oraclePhysicalSchema.setWorkSchemaName("ODI_TEMP_11G");

// Create the logical schema and the mapping to it in the context

OdiLogicalSchema oracleLogicalSchema = new OdiLogicalSchema(oracleTechnology, "HR");
new OdiContextualSchemaMapping(context,oracleLogicalSchema, oraclePhysicalSchema);

// Saving the Data Server , Physical and Logical Schema in  ODI

odiInstance.getTransactionalEntityManager().persist(oracleTechnology);

As we have mentioned before we need API documentation to pass the correct arguments . After execution of the above codes

as you can see that Data Server , Physical and Logical schema is created and the connection is successful.

image

image

This post was more about getting started with ODI SDK codes and as odiexperts have always tried to provide the easy tutorial to make ODI an easy experience and this post was meant to get started with configuration and setup of ODI SDK in eclipse  to write more easy and  fun SDK codes.

More SDK codes are coming soon.  🙂

The Complete code

package odi.sdk;

import oracle.odi.core.OdiInstance;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.TransactionCallbackWithoutResult;
import oracle.odi.core.persistence.transaction.support.TransactionTemplate;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiContextualSchemaMapping;
import oracle.odi.domain.topology.OdiDataServer;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.OdiPhysicalSchema;
import oracle.odi.domain.topology.OdiTechnology;
import oracle.odi.domain.topology.finder.IOdiTechnologyFinder;
import oracle.odi.domain.util.ObfuscatedString;
import oracle.odi.publicapi.samples.SimpleOdiInstanceHandle;

public class MyFirstSKDCode {

private static OdiDataServer oracleDataServer;
protected static String pStringToObfuscate;
protected static OdiContext context;

/**
* @param args
*/
public static void main(String[] args) {

final SimpleOdiInstanceHandle odiInstanceHandle = SimpleOdiInstanceHandle
.create("jdbc:oracle:thin:@localhost:1521:orcl",
"oracle.jdbc.OracleDriver",
"ODI_MASTER_11G",
"ODI_MASTER_11G",
"WORKREP1",
"SUPERVISOR",
"SUNOPSIS");

final OdiInstance odiInstance = odiInstanceHandle.getOdiInstance();
try {
TransactionTemplate tx = new TransactionTemplate(odiInstance.getTransactionManager());
tx.execute(new TransactionCallbackWithoutResult()
{
   protected void doInTransactionWithoutResult(ITransactionStatus pStatus)
   {

      //
     //  ODI SDK Codes goes here
    //

OdiProject proj = new OdiProject("XMT", "XMT");

OdiContext context = new OdiContext("XMT"); // New Context
context.setDefaultContext(true);

 // Creating a New Folder

OdiFolder fold = new OdiFolder(proj, "FOLDER");

 // Persist Project and Context

odiInstance.getTransactionalEntityManager().persist(proj);
odiInstance.getTransactionalEntityManager().persist(context);

 // create a Data Server , Physical Schema, Logical Schema

 // First lets make sure if we have the Oracle Technology

OdiTechnology oracleTechnology = ((IOdiTechnologyFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiTechnology.class)).findByCode("ORACLE");
if (oracleTechnology != null)

{
     oracleDataServer = new OdiDataServer(oracleTechnology,"ORCL_TARGET");

    // connection settings
    oracleDataServer.setConnectionSettings(new OdiDataServer.JdbcSettings(
       "jdbc:oracle:thin:@localhost:1521:orcl","oracle.jdbc.driver.OracleDriver"));

   // UserName and Password
   oracleDataServer.setUsername("HR");
   oracleDataServer.setPassword(ObfuscatedString.obfuscate("HR"));

}

// Creating a New Physical Schema

OdiPhysicalSchema oraclePhysicalSchema = new OdiPhysicalSchema(
oracleDataServer);
// Set additional information on the schema
oraclePhysicalSchema.setSchemaName("HR");
oraclePhysicalSchema.setWorkSchemaName("ODI_TEMP_11G");

// Create the logical schema and the mapping to it in the
// context
OdiLogicalSchema oracleLogicalSchema = new OdiLogicalSchema(oracleTechnology, "HR");
new OdiContextualSchemaMapping(context,oracleLogicalSchema, oraclePhysicalSchema);

// Persist the Data Server , Physical and Logical Schema

odiInstance.getTransactionalEntityManager().persist(oracleTechnology);

System.out.println("Done");

}
});
}

finally

{
odiInstanceHandle.release();
}

}

}

March 27, 2011
by kdevendr
21 Comments

Categories: Administration , Architecture , How to , Knowledge Modules , ODI , Technology , Tips and Tricks

Tags: ,

Single Post View


CDC CONSISTENT

Hi Friends,

It’s a post about one of the most questioned subjects that we receive.

I hope you all enjoy once it’s a very detailed one….

CDC consistent is a concept where Journalizing is carried at the Model level . As you all are aware of the point that in ODI CDC are of two types namely Simple and Consistent. We have already covered the Simple part early . You can visit this link for simple CDC steps. –http://odiexperts.com/changed-data-capture-cdc

CDC Consistent is great when we have tables in a schema related to each other via PK – FK relationship, as CDC Consistent are applied on the Model rather than on the Data store level as practiced in Simple.

Please find the step by step instruction of initial set up of CDC Consistent

Step 1. Edit the Model and select the appropriate Consistent Journalizing mode and select the appropriate Consistent KM.

image

Step 2. Add the data stores into CDC.

image

image

image

As you can see the all the data stores will have the orange clock shaped icon.

Step 3. Add the subscriber(s)

image

image

image

image

Step 4– Start the Journal

image

image

image

Once the Journalizing is successful the Orange clock icon will change to Green icon.

Testing the CDC Consistent on various scenarios.

Simple Insert is being carried at the Source Table.

image

Data being shown at the Journalizing data option.

image

Lets see if the Journalized data gets flow through.

image

The records does not seems to come through . Let’s see the issue or the real reason behind it.

image

The source table for the journalizing is reading from the JV$ view, where as the data seems to be reading from the JV$D view.

The real reason behind  why ODI reads from the JV$ rather than JV$D is that JV$ captures all the records from the JV$D by locking the subscriber.

Consider this example  say  at 10 pm there are 3 Inserts , 5 Updates and 1 deletes are happening at the source side  and thus gets captured in the  respective JV$D views .

We apply the Extend windows and Lock Journalize table and all the records in the JV$D gets captured into JV$.Now at that time there is again a new 1 insert and so gets captured in the JV$D views.  Since there is lock in the subscriber table  . New data will not be captured into JV$ .

Let’s repeat the necessary steps and rerun the interface.

Step 1. Extend Window

image

image

Step 2. Lock Subscriber

image

Select the right subscriber

image

image

Step 3– Run the interface

image

Step 4 – Unlock  Subscriber

image

image

Step 5 –  Purge Journal

image

image

Once the data is been captured the journal is purged.

image

Lets see how we can process the above steps via Package.

image

Step 1 – OdiWaitforLogData

image

Apart from the default setting  provide the Logical Schema and either provide the table name or the CDC Set.

Step 2 – Extend and Lock Subscribers the model

Drag and drop the Model into the Package and in the Type select Journalizing Model

and check mark Extend windows and Lock Subscribersimage

Step 3–  Interfaces

Now call the interfaces.

Step 4 – Unlock Subscribers and Purge Journal.

Next steps after the interface drag and drop the model and select Purge Journal and Unlock Subscribers.

image

Lets test the package . I am inserting another record and let see if the records come through .

image

image

Yes data has been inserted into the Target table.

image

Single Journalized table per interface Limit.

If there are n number of Journalized table in an interface , only  one interface can be marked as Journalized data , even if they are consistent based .

image

In order to avoid this limit , I have revised the code and re written it like this, so that we can use two journalized table without requiring to read through the complete table of the other source.

Note – There is a big assumption I am making that both the tables namely parent and child are dependent on each other  via PK-FK and so if there is an insert in one the of the Parent  table then there will be insert into the child table too

<% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>

<%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>.JRN_FLAG IND_UPDATE <%} else {%>'I' IND_UPDATE <%}%>

from

<%=odiRef.getSrcTablesList("", "[WORK_SCHEMA].<? if (u0022[IND_JRN]u0022.equals(u00221u0022)){?>JV$[TABLE_NAME] [TABLE_NAME] <?} else {?>[TABLE_NAME] [TABLE_NAME]<?}?> ", " , ", "")%>

where (1=1)

<%=odiRef.getJoin(i)%>

<%=odiRef.getFilter(i)%>

<%=odiRef.getSrcTablesList(" ", "AND [TABLE_NAME].<? if (u0022[IND_JRN]u0022.equals(u00221u0022)){?>JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */ <?} else {?><?}?> ", " ", "")%>

<%=odiRef.getGrpBy(i)%>

<%=odiRef.getHaving(i)%>

<%}%>

In order to use the above code in any KM.

Mark the parent Datastore with Journalized data store option.

Replace the code JRN_FLAG IND_UPDATE with <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>.JRN_FLAG IND_UPDATE

so it can use the Parent JRN_FLAG to accordingly drive the target.

 

Replace the code <%=odiRef.getFrom(i)%> with <%=odiRef.getSrcTablesList("", "[WORK_SCHEMA].<? if (u0022[IND_JRN]u0022.equals(u00221u0022)){?>JV$[TABLE_NAME] [TABLE_NAME] <?} else {?>[TABLE_NAME] [TABLE_NAME]<?}?> ", " , ", "")%>

so if the datastore is journalized then its associated JV$ view is used else the table name

Similarly replace <%=odiRef.getJrnFilter(i)%> with <%=odiRef.getSrcTablesList(" ", "AND [TABLE_NAME].<? if (u0022[IND_JRN]u0022.equals(u00221u0022)){?>JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */ <?} else {?><?}?> ", " ", "")%>

Here for this example i have used the hard coded  subscriber name of SUNOPSIS , you can use the getoption and pass the subscriber name in the Interface option.

The main reason for re writing it this way is that , when we have only one table as journalized , ODI have to read through the other child tables even though they are Journalized.

Lets see an example of the above code

SOURCE

ScreenClip(4)

Two new region id is inserts into Regions table with region id 7 and 8

ScreenClip(5)

Record is insert  with country _id ID  and region id 7

Looking at the combination and the Location parent driving then both will be inserted into JV$D views for the respective table.

ScreenClip(2)

ScreenClip(3)

TARGET

As you can see for the country id and region id 7 , the target table has correctly fetched the associated Region name .

ScreenClip(6)

Hope this article gives you a better understanding on the CDC Consistent type.

Thank you and please, keep visiting us.

March 2, 2011
by kdevendr
1 Comment

Categories: Administration , Architecture , Drivers , How to , Logic , ODI , Technology , Tips and Tricks

Tags: , , ,

Single Post View


Ordered SQL

Howdy, Friends…

A small but very useful tip!

There are some time we would need to process SQL (or load) via  a particular order and, to achieve that, many time the Developer would load the data into temporary tables and process them same later into Target.

We can change the way that joins are generated at Oracle Data Integrator (10g or 11g).

If we are dealing with latest Oracle database , there is option in ODI topology which when being enabled would enables us to do Ordered SQL and thus avoiding unnecessary temporary table and steps and even processing time.

To enable the Ordered SQL .

Go to Topology- > Physical Architecture – > Oracle Technology (or any technology you wish to change)

image

image

Interface

image

image

The option of the Ordered Join syntax is to be enabled and accordingly the order Number needs to specified.

ODI will process the joins based on the Order Number and also in the Ascending Order.

image

TABLE LENGTH

Just to add to the above technique , you can set the table length in the Technology settings.

image

Good to see you around… keep visiting us!