Need to know what all ETL jobs used the Oracle Tables
Moderators: chulett, rschirm, roy
Need to know what all ETL jobs used the Oracle Tables
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
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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
If you have a well maintained metadata repository, you can get this information by using "Usage Analysis" in the DS Manager client.
Whale.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
Mamu Kim
I would start here.
You may want to pick a specific job.
Code: Select all
select
DS_JOBOBJECTS.OLETYPE AS STAGE_TYPE FMT '20L',
Count(*) As Cnt
from
DS_JOBOBJECTS
group by
STAGE_TYPE
;
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
;
Mamu Kim
In the Datastage Administrator "command", in the Project Tab.
Or in your OS.
Or in your OS.
Last edited by narasimha on Tue Jan 23, 2007 3:37 pm, edited 1 time in total.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Mamu Kim
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
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