Extract Metadata Information from DS Metadata tables.

[Former Member]

Hello, 

I am trying to get information related to the Metadata for a particular job. I need the information like Object_name,Object_Type for a given Job name.

Looks like the metadata Tables of BODS are not Related to each other. so finding it tough to join Tables based on object names(which change from one table to another).

i need a query that can out put the information as below image.

Regards,

Rohil

[vipul_sharma4]

Hi Rohit,

I am unable to access the repository information in my Job.

Lets say i log in into my repository and now i want to access its information in my job/in a script.

I tried using SQL() but donot know what datastore i have to define to select repository table information.

Please help

[Former Member]

You can get the details from repo tables and views. I guess ALVW_FLOW stat captures all the information as above.

Arun

[Former Member]

I have tried it... made loads of possible changes to my query to match the object_name and type. But as i said before not all information is captured in single table. It would be great if any one can give me the exact query.

[Former Member]

Have you tried Impact and Lineage as well as Auto Documentation in the console. It captures various metadata details.

Arun

[Former Member]

Hi,

I can probably tell you the exact query if you can define your requirements a bit more clearly?

regards,

Adrian

[Former Member]

Say for eg.. I have a job which has a WF.. which inturn has a DF.. and in that DF i am loading data from one table to another.. i will have the usual Transform in between.

The Query should return me the Details like Object Name, Object Type

Object Name	Object Type
Job_Test_Load	Job
WF_CUST_Load	Work Flow
DF_CUST_Load	Data Flow
DS_SRC_ORA	Data Store
CUSTOMER	Table
DS_TGT_SQL	Data Store
CUST	Table
CASE_FILTER_US	Case
Q_FILTER_US	Query Transform
Q_FILTER_NON_US	Query Transform
MERGE_CUST	Merge

[Former Member]

The repository table you want is called al_parent_child, so a query such as

"select * from al_parent_child where parent_obj = 'Job_DM_Bank_IDoc'"

would return all objects for that job.

however, you also need to go down a level or two to discover objects referenced by any Workflows etc.

Also you have to be careful with this table - it is not always populated automatically by DS, but you can force it to update it using the al_engine command and the -ep option.

[Former Member]

Most of the tables look like they are all independent ones. They don't have any parent child relationship defined on them.. I can get information up till the Transforms, but from then it becomes complicated. The Object type defined in this table for most of the Transforms is "Transform" instead of say Case...,Table_Comparison etc.

I wrote couple of queries to get more information on the Transforms . Which i found in a view  "alvw_flow_stat". But joining these 2 just on the basis of Object Name doesn't always lead to correct values !!!

Please share if there is another way of getting this done. If possible a Data model of these tables and  views would do wonders...

Thanks,

Rohil

[Former Member]

Hi,

I think the answer to your question depends upon how important it is to have the information in the form you want it?  All the information is there in the repository tables - it is just not easy to get at!

So for example you can get the object_id for a DF from the above query and then go to either the AL_LANGTEXT or AL_LANGXMLTEXT tables and get the actual atl for that object.

Then 'all' you have to do is parse it looking for the transform objects, it can be done and I have written some code to do it in the past - but you have to remember that any code you write may not work in future versions of DS so you must make sure the effort is worth it.

regards,

Adrian