Howdy guys!

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.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.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
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 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";

        Project_Code="XMT";
        Context_Code="XMT";
        Folder_Name="FOLDER";
        source_model_name = "SRCE_HR";
        String[] source_datastore={"REGIONS","REGIONS","REGIONS"};
        String[] operator={"UNION","MINUS"};
        target_model_name = "TRGT_HR";
        target_datastore="REGIONS";

        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<OdiFolder> fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
        for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
            folder = (OdiFolder) it.next();
        }

        // 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
                intf.setOptimizationContext(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();
                dataset.setName("DATASET"+i);

            } else {
            dataset=new DataSet(intf,"DATASET"+i);
            dataset.setOperator(operator[i-1]);
            dataset.setOrder(order);
            }

            srcset = new SourceSet("SrcSet01",dataset);
            sd=new SourceDataStore(dataset,false,sourceDatastore.getName().toString(),0,sourceDatastore);
            srcset.addSourceDataStore(sd);
            dataset.addSourceSet(srcset);
            order+=10;

        }

        // 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(
                        LKM,Project_Code);

        for (Iterator<OdiLKM> 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", true));
            }
        }

        // IKM
        // Find the IKM using the IOdiIKMFinder

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

        for (Iterator<OdiIKM> 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<OdiCKM> ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
                CKM,Project_Code);

        for (Iterator<OdiCKM> 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 .."+ target_datastore );
        IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
        OdiScenario newScen = gene.generateScenario(intf,target_datastore, "001");
        odiInstance.getTransactionalEntityManager().persist(newScen);*/

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

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

}

As you know, comments are always really welcome!

Good to see you here.

Hi everyone…

This post is about a something new on ODI 11g…

I must confess that  when I saw the first time I didn’t get the concept but now, it’s more clear!

I hope you all enjoy!

In ODI 10g we had this big issue of killing the job especially its big headache when we need to ping or disturb DBA . ODI 11g seems to handle this situation smartly by introducing new options called STOP NORMAL and STOP IMMEDIATE.

You can find this option under the running session.Lets look how this two options work.

STOP NORMAL

I am killing the running session using the option STOP NORMAL. The SID for this query is 143  and so I right click and click on Stop Normal

A pop up will be shown for  Stop Session dialogue and click OK to kill it.

After running a few test I have seen that Stop Normal takes time to kill the session and  after a while we get the Session Stopped dialogue. Generally in the Normal  kill command,  Oracle marks the query with status of Kill so most of the time the Oracle will wait for the operation or query to be processed and mark the query as Killed and pretty much the similar logic is being carried out for the STOP NORMAL, so this kill operation takes time.

My SID query also return nothing as the Session is killed in the Database too.

STOP IMMEDIATE

Similar to the Stop Normal the  Stop Immediate will ask the permission to be killed or not . Click OK for the confirmation.

Being an Immediate clause ,  the query is killed immediately.

Note:- This process can only work when the User used in Topology have the necessary Kill  permissions.

That is it! I hope be useful to all of you!

Thanks for visiting us!

Few posts back i have shown ,how to display the value of the variable using Jython and today i am showing how to display  SQL  Query result  in the operator.

– Displays the Sql results of query into Operator -

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
result=sqlstring.executeQuery("select table_name,owner from all_tables where owner='ODI_TEMP'")
i=0
rs=[]
while (result.next()):
    rs.append(str(i)+'\t'+str(result.getString("table_name"))+'\t\t'+str(result.getString("owner"))+' , ')
    i=i+1

res='\n'.join(map(string.strip,rs))
raise res
sourceConnection.close()

In  sourceConnection = odiRef.getJDBCConnection(“SRC”), the connection details are retrieved from ODI , to do so in the ‘ command on  Source tab’  – Select the Technology and required Schema so that ODI can retrieve the connection details.

result=sqlstring.executeQuery(“select table_name,owner from all_tables where owner=’ODI_TEMP’”)

# Enter the query between the two double quotes or replace the above query .

rs.append(str(i)+’\t’+str(result.getString(“table_name”))+’\t\t’+str(result.getString(“owner”))+’ , ‘)

# For every column you mention in the above query – get them under result.getString(“Column_Name”)

# as for my example i am having two column so iam calling getString twice , similarly do so for every column

# called in the Select statement

In the Target technology select  ’  Jython ‘

Enable  the ‘ Ignore Errors’ option.

Sample Output

- Codes to display the Row count -

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
    count=result.getInt("rowcount")

rs='\n'+str(sqlstmt)+'\n\n'+str(count)
raise rs
sourceConnection.close()

Sample Output