DAC - Execution Plan, Subject Area, Task Group and Task

Nayas

Hi Everyone,

I am not sure if I am asking my question in the correct place.If so, please let me know where I should ask it.

I am new to Informatica / DAC and trying to better understand our main Execution Plan.

In our DAC, we have some Execution Plans, many Subject Areas, Task Groups and Tasks.

Is there a way, from SQL, to pull for a given Execution Plan, all the task groups and their sub tasks and tasks and their dependencies?

So far, I wrote this, which answer 80% of my needs, but I do not know/understand how to pull the task group and task associated to a task group. I am also not sure the "Depth" is the only dependencies that we have.

For TASK GROUP, what I am referring to, is from DAC, when we check our daily execution plan, and sort the tasks by duration, there is a task group that takes 9 hours. I would like to understand which tasks are in that task group and sum their duration to get that 9 hours.

select d.etl_defn_name    EXECUTION_PLAN,      r.depth            DEPTH,      s.name             TASK_GROUP_NAME,      r.step_name        TASK,      r.phy_folder_name  FOLDER,      r.phy_src_name     SRC_NAME,      r.phy_trgt_name    TRGT_NAME,      r.phase_name       PHASE_NAME,      to_char(r.start_ts,'DD-MON-YYYY HH:MI:SS') START_TIME,      to_char(r.end_ts,'DD-MON-YYYY HH:MI:SS') END_TIME,      round((r.end_ts - r.start_ts) * 86400,2) ELAPSED_TIME_SECS,      r.status,      r.sucess_rows      SUCCESSFUL_ROWS,      r.failed_rows      FAILED_ROWSfrom   rw_etl_defn_run d,      w_etl_run_step r,      w_etl_step swhere d.row_wid = r.run_widand   s.row_wid = r.step_widand d.etl_defn_name = '<EXECUTION_PLAN_NAME>'and d.start_ts >= to_date('<EXECUTION_PLAN_START_TS>','YYYY-MM-DD HH24:MI:SS')order by r.start_ts asc;