Get ODI Load Plan step name dynamically using ODI Substitution methods

ODI 11g provided the Load plan functionality to the users, along with its substitution methods to create generic objects in ODI.

Case: User requirement is to refresh multiple Materialized views from ODI Load plans.

1. Create ODI procedure to refresh the materialized view.

begin

dbms_snapshot.refresh('SCHEMA_NAME.MVIEW_NAME',ATOMIC_REFRESH=>FALSE);

end;

If we have hundreds of JOBs to execute, then we have to create those numbers of ODI Procedures. And same needs to be add into the load plans, which would generate extra overhead of JOB integrations in ODI.

Instead of that we would recommend to create a one generic/reusable ODI procedure and use that across the load plans, where you need to set the load plan step names with the help of <%=odiRef.getLoadPlanStepInstance("STEP_NAME")%> substitution method.

Add below code into the ODI Procedure.

begin

dbms_snapshot.refresh('SCHEMA_NAME. <%=odiRef.getLoadPlanStepInstance("STEP_NAME")%>’

,ATOMIC_REFRESH=>FALSE);

end;

2. Add the ODI Procedure into load plan and change the name of load plan steps according to the JOBs. The provided step name would get captured into that ODI procedure code at run-time.

3. Execute the load plan and see the logs for those executed jobs. Check the code of executed procedure where we can see the JOB name which we have provided into the load plan.

Cheers..!! :)

Configuration of ODI Java EE agent.

Create Physical agent in topology with following parameters:

Name= Agent name

Host=on which Application server is installed.

Port=on which application server runs.’

Web application context: default (oraclediagent)

Create Application Server Credentials store:

a.       Go to the <ODI_HOME>\common\bin and run the wlst.sh file.

b.      Wls:/offline>connect(‘weblogic’,’weblogic123’,’t3://localhost:7001’)

c.      Wls:/odi_11g/serverConfig>createCred(map="oracle.odi.credmap",key="SUPERVISOR",user="SUPERVISOR",password="odisuper",desc="key for supervisor")

d.     Wls:/odi_11g/serverConfig>disconnect()

Now Credential store is created for the application store and defined the SUPERVISOR KEY, which will be used while generating WLS template.

Generate the server template for JAVAEEAgent.

Before generating server template, define the data sources for the agent.

a. Drag the work repository the data server into this data source and define the JNDI names.

B. Generate the server template.

Go to that location where jar file is located e.g: /oradata/ODI_SETUP/

Run the jar -xvf WLST_AGENT.jar file.

Got to the /oradata/odi_home/agentapp location and see that oracledi.ear file is created. And Deploy this same file on that application server (e.g:odi_server1).

Now see the deployments of odi_server1: check weather that agent application state is active.

Test the Java EE agent connection from Topology; 

Yehhhh !!!!!! We have successfully configured ODI JAVA EE agent……Njjoy…..! J