Query for OBIA 11g Load Plan details

Nagadasari

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

Charles M

Hi Raghu,

Try this (use a connection to your ODI repository):

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from {your_odi_repository}.snp_lpi_run order by start_date desc;

Let me know if this is what you need.

Regards,

Charles

Charles M

Default duration is in seconds. You can make these modifications to convert that into minutes or hours:

-- "Duration in Minutes"
select I_LP_INST, NB_RUN, START_DATE, END_DATE, round((duration/60),2) as "Duration in Minutes" from {your_odi_repository}.snp_lpi_run order by start_date desc;

-- "Duration in Hours"

select I_LP_INST, NB_RUN, START_DATE, END_DATE, round(((duration/60)/60),2) as "Duration in Hours" from {your_odi_repository}.snp_lpi_run order by start_date desc;

Regards,

Charles

Charles M

Here are some of the repository tables that relate to load plans (based on 11.1.1.7, but should also work for 11.1.1.9):

SNP_LP_INST	Load Plan instance. When a load plan starts, this table acts as the running instance of this load plan
SNP_LP_STEP	Load Plan steps hierarchy
SNP_LP_STEP_VAR	Load plan variables defined for a step
SNP_LP_VAR	Load plan variables
SNP_LPI_EXC_LOG	Load plan instance exception log per run
SNP_LPI_EC__VAR_LOG	Load plan instance exception variable log per run
SNP_LPI_RUN	Load Plan instance runs. Every time an attempt is made to re-start a load plan, data is captured here
SNP_LPI_STEP	Load plan instance steps hierarchy
SNP_LPI_STEP_LOG	Load Plan instance step logs per run
SNP_LPI_STEP_VAR	Load plan instance variables defined for a step
SNP_LPI_TXT_LOG	Load plan instance variables SQL text log per run [DEPRECATED]
SNP_LPI_VAR	Load plan instance variables
SNP_LPI_VAR_LOG	Load plan instance variables log per run

Nagadasari

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

John_K

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

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.