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 11.1.1.6 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 11.1.1.6: 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())
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)

//--------------------------------------
// 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);
    return
}

//--------------------------------------
// 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')
      }
      println("\t")
}