BODS SQL Query for template table and permanent table

[abhishekbiwal]

Hello experts,

Any clue, how do we find out permanent table and template tables, for a given repository or for a given project.

Thank and regards,

Abhishek Biwal

[Former Member · Accepted solution]

I am not sure but there is no way you can directly get the details required by ypou.

You can check the same in BODS designer by clicking on repository tab & chose template table.

Thanks,

SB

[abhishekbiwal · Accepted solution]

Hello Swapnil,

I know there is no direct way of find it out.
not sure, which metadata tables to use and what all join conditions would be needed to find the permanent tables in a given project.

Thanks and regards,

Abhishek Biwal

[severin_thelen · Accepted solution]

That would be the easy way.

I have check the AL_Tables another time and in the AL_PARENT_CHILD table you just could find parent jobs but no parent projects. But the big problem is, that you have to work with regression / iteration, because you do not know how many steps you need to reach the top element. And to do this in SQL is very difficult.

I would prefer a name convention, where you could identify the project name about the object (JOB, WF,DF) names. Then you could select the correct tables about DF names. Maybe you could do this with the object description, too.

Regards

Severin

[abhishekbiwal · Accepted solution]

Hello Severin,

the naming convention we follow is, any project must start with PRJ_Customer_Master
Dataflow DF_Customer_Master_KNA1, DF_Customer_Master_KNB1, DF_Customer_Master_KNB5,
workflow with WF_Customer_Master

thanks and regards,

Abhishek Biwal

[severin_thelen · Accepted solution]

Maybe the following should work. You just need to replace Customer and Master with your needed searching string.

SELECT t3.PARENT_OBJ, t1.NAME, t2.ATTR_NAME, t2.ATTR_VALUE

FROM DS_REPO_SET.dbo.AL_SCHEMA t1

JOIN DS_REPO_SET.dbo.AL_ATTR t2 on t1.OBJECT_KEY=t2.PARENT_OBJID

JOIN DS_REPO_SET.dbo.AL_PARENT_CHILD t3 on t3.DESCEN_OBJ_KEY=t1.OBJECT_KEY

WHERE t2.ATTR_NAME='Loader_Is_Template_Table'

AND t3.PARENT_OBJ='DF_'+Customer+'_'+Master

Regards

Severin

[abhishekbiwal · Accepted solution]

Hello Severin,

This helps a lot, but gives zeros records.

We came up with query,             

SELECT SRC.* FROM (   

select
AL_USAGE.PARENT_OBJ,AL_USAGE.PARENT_OBJ_TYPE,AL_SCHEMA.OBJECT_KEY,

AL_SCHEMA.NAME,AL_SCHEMA.OWNER,AL_SCHEMA.DATASTORE
from <DS_DM_USER_NAME>.AL_SCHEMA

  left outer join <DS_DM_USER_NAME>.AL_USAGE

  on AL_SCHEMA.NAME=AL_USAGE.DESCEN_OBJ and
AL_SCHEMA.OWNER=AL_USAGE.DESCEN_OBJ_OWNER and
AL_SCHEMA.DATASTORE=AL_USAGE.DESCEN_OBJ_DS

  where AL_USAGE.PARENT_OBJ_TYPE = 'Job'

  AND AL_USAGE.DESCEN_OBJ_TYPE = 'Table'

  --AND AL_USAGE.DESCEN_OBJ_USAGE in ('Target','Source')

  and AL_USAGE.PARENT_OBJ_KEY in (select distinct JOB_ID from
<DS_DM_USER_NAME>.ALVW_PROJECT_JOB where PROJECT_ID=(select max(OBJECT_KEY) from
<DS_DM_USER_NAME>.AL_PROJECTS where NAME='PR_A_VENDOR_MASTER')))
SRC

  left outer join <DS_DM_USER_NAME>.AL_ATTR

  on SRC.OBJECT_KEY = AL_ATTR.PARENT_OBJID

  WHERE

AL_ATTR.ATTR_NAME = 'Loader_Is_Template_Table' AND
UPPER(AL_ATTR.ATTR_VALUE) = 'NO' AND AL_ATTR.PARENT_OBJ_TYPE = '7'

and not(SRC.NAME like 'A_%') and not(SRC.NAME like
'VMAP_%') 

order by 1 desc;

You need to replace, DS_DM_USER_NAME with your local repo name, and PR_A_VENDOR_MASTER, with your porject name.

thanks to my teammate

thanks and regards

Abhishek Biwak

[severin_thelen · Accepted solution]

Ups, I forgot to replace my custom repo name .

But I am happy, that you find a working solution.

Regards

Severin

[mageshwaran_subramanian]

Have a look at this.

[severin_thelen]

Hello Biwal,

you could do it, with the following SQL query.

SELECT t1.NAME, t2.ATTR_NAME, t2.ATTR_VALUE

FROM DS_REPO_SET.dbo.AL_SCHEMA t1

JOIN DS_REPO_SET.dbo.AL_ATTR t2 on t1.OBJECT_KEY=t2.PARENT_OBJID

WHERE t2.ATTR_NAME='Loader_Is_Template_Table'

In AL_SCHEMA you could find all tables and in AL_ATTR you could find all attributes. So you have to join both tables (OBJECT_KEY==OBJID).

You could restrict the result about the WHERE clause. You should find the connection to the project about the AL_PARENT_CHILD table. Maybe you have to use a another table to get the ID of your project name.

Regards

Severin

[abhishekbiwal]

Hello Severin,

thank you for the reply, but this is a very extensive list.
My needs is to find permanent tables in a given Project in my local repository.

Thanks and regards,

Abhishek Biwal