ODI SDK: Retrieving Information From the Logs

It is fairly common to want to retrieve data from the ODI logs: statistics, execution status, even the generated code can be retrieved from the logs.

The ODI SDK provides a robust set of APIs to parse the repository and retreve such information. To locate the information you are looking for, you have to keep in mind the structure of the logs: sessions contain steps; steps containt tasks.

The session is the execution unit: basically, each time you execute something (interface, package, procedure, scenario) you create a new session.

The steps are the individual entries found in a session: these will be the icons in your package for instance. Or if you are running an interface, you will have one single step: the interface itself.

The tasks will represent the more atomic elements of the steps: the individual DDL, DML, scripts and so forth that are generated by ODI, along with all the detailed statistics for that task.

All these details can be retrieved with the SDK.

Because I had a question recently on the API ODIStepReport, I focus explicitly in this code on Scenario logs, but a lot more can be done with these APIs.

Here is the code sample (you can just cut and paste that code in your ODI Groovy console). Just save, adapt the code to your environment (in particular to connect to your repository) and hit "run"

//Created by ODI Studio
import oracle.odi.core.OdiInstance
import oracle.odi.core.config.OdiInstanceConfig
import oracle.odi.core.config.MasterRepositoryDbInfo
import oracle.odi.core.config.WorkRepositoryDbInfo
import oracle.odi.core.security.Authentication 
import oracle.odi.core.config.PoolingAttributes
import oracle.odi.domain.runtime.scenario.finder.IOdiScenarioFinder
import oracle.odi.domain.runtime.scenario.OdiScenario
import java.util.Collection
import java.io.*

/* -----------------------------------------------------------------------------------------
Simple sample code to list all executions of the last version of a scenario,
along with detailed steps information
----------------------------------------------------------------------------------------- */

/* update the following parameters to match your environment
=> */
def url = "jdbc:oracle:thin:@myserver:1521:orcl"
def driver = "oracle.jdbc.OracleDriver"
def schema = "ODIM1116"
def schemapwd = "ODIM1116PWD"
def workrep = "WORKREP1116"
def odiuser= "SUPERVISOR"
def odiuserpwd = "SUNOPSIS"

// Rather than hardcoding the project code and folder name,
// a great improvement here would be to parse the entire repository

def scenario_name = "LOAD_DWH" /*Scenario Name*/

/* <=
End of the update section */

//Connection to the repository
// Note for ODI you could use predefined odiInstance variable if you are
// running the script from a Studio that is already connected to the repository

def masterInfo = new MasterRepositoryDbInfo(url, driver, schema, schemapwd.toCharArray(), new PoolingAttributes())
def workInfo = new WorkRepositoryDbInfo(workrep, new PoolingAttributes())
def odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo))

// In all cases, we need to make sure we have authorized access to the repository
def auth = odiInstance.getSecurityManager().createAuthentication(odiuser, odiuserpwd.toCharArray())

// Retrieve the scenario we are looking for
def odiScenario = ((IOdiScenarioFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiScenario.class)).findLatestByName(scenario_name)

if (odiScenario == null){
    println("Error: cannot find scenario "+scenario_name);

// Retrieve all reports for the scenario
def OdiScenarioReportsList = odiScenario.getScenarioReports()

println("*** Listing all reports for Scenario \""+scenario_name+"\" ")

// For each report, print the folowing:
// - start time
// - duration
// - status
// - step reports: selection of details

for (s in OdiScenarioReportsList){
        println("\tStart time: " + s.getSessionStartTime())
        println("\tDuration: " + s.getSessionDuration())
        println("\tStatus: " + s.getSessionStatus())
        def OdiScenarioStepReportsList = s.getStepReports()
        for (st in OdiScenarioStepReportsList){
            println("\t\tStep Name: " + st.getStepName())
            println("\t\tStep Resource Name: " + st.getStepResourceName())
            println("\t\tStep Start time: " + st.getStepStartTime())
            println("\t\tStep Duration: " + st.getStepDuration())
            println("\t\tStep Status: " + st.getStepStatus())
            println("\t\tStep # of inserts: " + st.getStepInsertCount())
            println("\t\tStep # of updates: " + st.getStepUpdateCount()+'\n')


Hi Christophe,

Its very nice to see such explanation using ODI SDK. I have tested above codes and its working absolutely fine.It would be better if you can add one more step to get the error message back (using getStepErrorMessage()) for current execution of a scenario not for all previous execution.


Posted by Ashok on April 02, 2012 at 04:49 PM PDT #


There are two approaches you can take.
1. You can simply use a substitution API called getPrevStepLog to retrieve the error message of a previous step in the current scenario, wihout needing the whole SDK coding environment: <%=odiRef.getPrevStepLog("MESSAGE")%> will retrieve the error message.
2. If you are using the SDK and want to retrieve the error for the current scenario, you are probably better off working with sessions than scenarios. The current session number can be retrieved with <%=odiRef.getSession("SESS_NO")%>. From there you can use IOdiSessionFinder.findBySessionId() and simply use getErrorMessage() on that session.
Either approach should work!

My best

Posted by Christophe on April 10, 2012 at 11:18 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« November 2015