Finding a certain Target table in the BODI repository tables

dataservices metadata reports
1

Hi,

There is a target table, used in a dataflow but this dataflow is not (yet) in a job or workflow. When I look in the AL_PARENT table this table cannot be found! Or am i wrong?

Which repository table should I use to find a table anyway whether it is used in a job or not?

The script I use to breakdown/flatten the AL_PARENT table looks like this :

select j.parent_obj_key as Jobkey, j.parent_obj as Job, wf.parent_obj_key as WFKEY, wf.parent_obj as Workflow, df.parent_obj_key as DFKEY ,
df.parent_obj as Dataflow, df.descen_obj_type, df.descen_obj as Tabel_or_Datastore_name ,df.descen_obj_usage as Source_or_Target
from pr_repo.al_parent_child j, pr_repo.al_parent_child wf, pr_repo.al_parent_child df
where j.parent_obj_type = ‘Job’
and j.descen_obj_key= wf.parent_obj_key
and wf.parent_obj_type = ‘WorkFlow’
and wf.descen_obj_key= df.parent_obj_key
and df.parent_obj_type = ‘DataFlow’
and df.descen_obj_usage in (‘Source’,‘Target’)

==================================
but I think this only works when you search for Dataflows or Workflows that are in a job?! :stuck_out_tongue:

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

2

Hi

Your query will give the parent child relationship only (I mean for only 1 level)

Probably you should use the “CONNECT BY PRIOR” to arrive at the entire chain from top level to the bottom level in hierarchy

AL_PARENT_CHILD will just give the parent child relationship for each single item in the repository. Once you make the chain from Job to Tables you can find which are used really.

ganeshxp :us: (BOB member since 2008-07-17)

3

Hi,

I tried the following :

SELECT parent_obj,
SYS_CONNECT_BY_PATH (parent_obj,’/’) path
FROM pr_repo.al_parent_child pc
where pc.parent_obj_type = ‘Job’
CONNECT BY PRIOR pc.parent_obj_key= pc.descen_obj_key
ORDER BY 2

This works but …again two questions

  1. How deals Connect by prior with null values due to an unbalanced tree. E.g. there are workflows in a job, the first one uses a Dataflow, the other not. Or said in another way, a workflow does not have a dataflow beneath.

  2. What happens with nested workflows? Are they also presented?

TurningPointHolland :netherlands: (BOB member since 2006-09-06)