Query for OBIA 11g Load Plan details

Question

Hi Friends,

We are running multiple load plans in OBIA 11g. Now my customer want to see their no.of load plans, start date, end date, duration in hh:mm:ss format. Can anyone please give me query with metadata table names.

Thanks in advance.

Raghu Nagadasari

Nagadasari · Answer

Thanks ORA-01033/John Keymer for the information. Could you please share the app to raghu.nagadasari@gmail.com so that we can try to implement this. Thanks again.

John_K · Answer

As this is customer facing, have you thought about putting this in a small app on the ODI server? I started something a while back but never got around to finishing it - http://wegobeyond.co.uk/blog/entry/monitoring-oracle-data-integrator-odi-load-plan-executions-with-bi-apps.html. You can make it quite powerful though - allow users to drill into different load plan executions, analyse long running jobs, jobs whose time has increased consistently, historic failures etc.

Nagadasari · Answer

Thanks Charles M for you prompt response. We got below query from my friend.

Execute the below Query in the ODIREPO Schema of the respective instance:

SELECT --SSF.SCEN_FOLDER_NAME,

SLP.I_LOAD_PLAN,

SLP.LOAD_PLAN_NAME,

RT.I_LP_INST,

RT.NO_OF_RUNS,

RT.START_TIME,

RT.END_TIME,

RT.total_duration

FROM SNP_LOAD_PLAN SLP,

SNP_SCEN_FOLDER SSF,

SNP_LP_INST SLI,

(SELECT i_lp_inst,

MAX(NB_RUN) No_of_Runs,

MIN(TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI:SS')) start_time,

MIN(START_date) START_TIME_DT,

MAX(TO_CHAR(end_date, 'DD-MON-YYYY HH24:MI:SS')) end_time,

TO_CHAR(to_date(SUM(duration),'sssss'),'hh24:mi:ss') total_duration

FROM SNP_LPI_RUN

GROUP BY i_lp_inst

) RT

WHERE SLP.I_SCEN_FOLDER  =SSF.I_SCEN_FOLDER

AND SLP.I_LOAD_PLAN      = SLI.I_LOAD_PLAN

AND SLI.i_lp_inst = RT.i_lp_inst

AND SLP.FIRST_USER      IN ('biappsadmin')

AND SSF.SCEN_FOLDER_NAME = 'Generated Load Plans'

ORDER BY --SLP.I_LOAD_PLAN , RT.i_lp_inst

RT.START_TIME_DT;

Thank you Ravi for the detailed query.

Thanks,

Raghu Nagadasari