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 Studioimport oracle.odi.core.OdiInstanceimport oracle.odi.core.config.OdiInstanceConfigimport 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 repositorydef auth = odiInstance.getSecurityManager().createAuthentication(odiuser, odiuserpwd.toCharArray())odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)
//--------------------------------------// Retrieve the scenario we are looking fordef 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")}
