Need to know what all ETL jobs used the Oracle Tables

[Titto]

HI,

Is there a way to find out in PX, what all ETL jobs been used a particular Table or File?

As we are using ODBC section and used User Defined SQL's.

Any Help is appreciated.

thanks

[I_Server_Whale]

If you have a well maintained metadata repository, you can get this information by using "Usage Analysis" in the DS Manager client.

Whale.

[DSguru2B]

Kim has something like that which can tell you what table is being used up. Search this forum for etlstats.

[vmcburney]

The usage analysis function in version 7.x doesn't really work that well. It is better in version 8. You would hope that the table names in your Oracle stages are accurate even though they are superseded by the user-defined SQL.

You could extract all your jobs to an XML output file and view it using an xsl stylesheet to only show the information you are interested in. Have a look at the xsl files in the DataStage client directory for examples. You could take one of these and remove all the code leaving just the Oracle plugin code and the properties you are interested in, all database table names for example.

A faster way is to get the dwnav from Kim Duke's website.

[ray.wurlod]

I disagree about Usage Analysis, provided that you have been meticulous with your table definitions and not broken the nexus between the table definition in the repository and the Job(s) in which it has been used.

[kduke]

You need to figure out what your stage types are. I would need to research where the SQL is stored. I wrote a routine once which can extract table names from SQL. I would need to go look for it. I was written for Oracle. It used a list of all the Oracle keywords to parse the SQL. I wrote it at BellSouth about 4 years ago or more. How bad you want this?

[kduke]

I would start here.

Code: Select all

select 
   DS_JOBOBJECTS.OLETYPE AS STAGE_TYPE FMT '20L',
   Count(*) As Cnt
from 
   DS_JOBOBJECTS
group by 
   STAGE_TYPE
;

You may want to pick a specific job.

Code: Select all

select 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L',
   DS_JOBOBJECTS.OLETYPE AS STAGE_TYPE FMT '20L'
from 
   DS_JOBOBJECTS, 
   DS_JOBS
where 
   DS_JOBS.NAME = 'BuildDayDimHash' 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
;

[pavankvk]

Kduke

where can e execute such queries? and where can i get all such information on DS_* in the universe?

[narasimha]

In the Datastage Administrator "command", in the Project Tab.
Or in your OS.

[ray.wurlod]

THere is, theoretically at least, an infinite amount of knowledge in the universe, therefore it is impossible to get it all. Infinity is unattainable.

[kduke]

I prefer to telnet into the DataStage server instead of using Administrator. You need to get to TCL. Search for TCL commands and you can see a lot of posts. EtlStats includes a lot of these SQL statements. My tips page now has a link to the EtlStats install video. This explains how to do TCL commands.

[DSguru2B]

You can also use DSExecute() if your thinking of doing this via routine.

[kumar_s]

Kim, all these will give the list of jobs and stages based on jobs and vise versa. But OP is looking fetch the Stages or Jobs based on the Table that been used, isn't?

[kduke]

That is my point. I have not done this in PX. I am working with PX but to hack a solution then I need to know what OLETYPEs they are using. I should be able to figure it out.

[Titto]

Hello Guys
thanks for the different kind of inputs and solutions. Kim, We need this to be done with out high priority but we need to have this cross referrence some time next month.
I will try doing some solutions provided.
Keep you posted, but if any one has come up with good solutions please share.

Thanks