Jobs Traceability Matrix - Query in BODS

[Former Member]

All the jobs and its associated component details can be retrieved by executing Query against below metadata tables

Database:  <Repository DB> <Repository login>

Ex: UBIBOR01  d2_14_loc

Tables: ALVW_PARENT_CHILD, AL_PARENT_CHILD, AL_LANG, AL_USAGE, etc.

This is a query which will list all the jobs and their traverse paths till Source / Target table

select Connection_Path.PATH || Other_Objects.DESCEN_OBJ || '( ' || Other_Objects.DESCEN_OBJ_USAGE || ' ) ' PATH
     , substr(Connection_Path.PATH, 2 , instr(Connection_Path.PATH, ' ->> ', 2)-2) Job_Name

FROM
(
SELECT DISTINCT PARENT_OBJ
      , PARENT_OBJ_TYPE
      , SYS_CONNECT_BY_PATH(PARENT_OBJ,' ->> ')|| ' ->> ' PATH
FROM ALVW_PARENT_CHILD
START WITH PARENT_OBJ_TYPE = 'Job'
CONNECT BY PRIOR DESCEN_OBJ = PARENT_OBJ
) Connection_Path,
(
SELECT  PARENT_OBJ
      , PARENT_OBJ_TYPE
      , DESCEN_OBJ
      , DESCEN_OBJ_USAGE
FROM ALVW_PARENT_CHILD
WHERE PARENT_OBJ_TYPE = 'DataFlow'
and
DESCEN_OBJ_TYPE = 'Table'
)Other_Objects
WHERE
Connection_Path.PARENT_OBJ = Other_Objects.PARENT_OBJ
AND
Connection_Path.PARENT_OBJ_TYPE = Other_Objects.PARENT_OBJ_TYPE