Way to Retrieve the Transformations of a Job
Moderators: chulett, rschirm, roy
Way to Retrieve the Transformations of a Job
Hi Guys,
I am trying to do reverse engineering. I am trying to retrieve all the transformations of each job present in my project.
Is there command/routine to retrieve all the transformations happening in a job.
Cheers!!!
I am trying to do reverse engineering. I am trying to retrieve all the transformations of each job present in my project.
Is there command/routine to retrieve all the transformations happening in a job.
Cheers!!!
Datastage Developer cum ETL designer.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Another option - leverage InfoSphere DataStage job reporting.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Guys, may be i have not explained properly. I am trying to figure out given a job name, i want to retrieve all the source columns and their associated transformations and their respective target columns.
I am Looking at XMETA now. I am able to list all the stages associated with each job using the below query.
But now i am looking at joingin the above query with datastagex_dstransformc2e76d84 table.
Can somebody help me in identifying the key column. (or i should first join the above query with Link table and then join with Transform table)?
Cheers!!
I am Looking at XMETA now. I am able to list all the stages associated with each job using the below query.
Code: Select all
select * from datastagex_dsjobdefc2e76d84 a, datastagex_dsstagec2e76d84 b
where b.xmeta_repos_object_id_XMETA =a.container_rid and
and a.NAME_XMETA='<JobName>'
Can somebody help me in identifying the key column. (or i should first join the above query with Link table and then join with Transform table)?
Cheers!!
Datastage Developer cum ETL designer.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In this thread, Job Type is marked as Parallel.
Table datastagex_dstransformc2e76d84 seems to contain information about DataStage Transforms only. These transforms are available for use in DataStage BASIC expressions only e.g. server jobs and sequences. Only way of using these in Parallel jobs is thru Basic Transformer stage. I don't think that this table has the information that you need.
Table datastagex_dstransformc2e76d84 seems to contain information about DataStage Transforms only. These transforms are available for use in DataStage BASIC expressions only e.g. server jobs and sequences. Only way of using these in Parallel jobs is thru Basic Transformer stage. I don't think that this table has the information that you need.
Yes, Table datastagex_dstransformc2e76d84 contains only Transform functions.
I have almost found the solution.
So, i have joined the below four tables and got all the stages and links present in each job.
And i guess DSFLOWVARNAMES_XMETA is the source column name and DSFLOWVARPROPS_XMETA contains all the transformations which i have decipher using some logic.
But i am getting values for only 2 links out of 16 links present in the job for all other links it is giving null value.
Can somebody help me to understand what i am doing wrong.
Cheers!!!
I have almost found the solution.
So, i have joined the below four tables and got all the stages and links present in each job.
Code: Select all
datastagex_dsjobdefc2e76d84 a, datastagex_dsstagec2e76d84 b,datastagex_dsoutputpinc2e76d84 c, datastagex_dslinkc2e76d84 d
But i am getting values for only 2 links out of 16 links present in the job for all other links it is giving null value.
Can somebody help me to understand what i am doing wrong.
Cheers!!!
Datastage Developer cum ETL designer.
Ray, the problem we have only individual jobs and no sequencer. The sequencing is done in Control-M scheduler.. So, not sure if data lineage for a single job can be done.ray.wurlod wrote:If you have Metadata Workbench you can request a data lineage report that will give you the transformations. ...
Datastage Developer cum ETL designer.
What is the query that you are using to join these tables?sam paul wrote:So, i have joined the below four tables and got all the stages and links present in each job.
Code: Select all
datastagex_dsjobdefc2e76d84 a, datastagex_dsstagec2e76d84 b,datastagex_dsoutputpinc2e76d84 c, datastagex_dslinkc2e76d84 d
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Following is the query i am using. But the Transformation column is coming as Null.
Code: Select all
select a.DSNAMESPACE_XMETA Projname,a.category_xmeta folder_name, a.NAME_XMETA as Job_Name,b.name_xmeta as Stage_Name,
d.name_xmeta Link_Name,e.sourcecolumn_xmeta Target_Column,e.expression_xmeta Transformation
from datastagex_dsjobdefc2e76d84 a, datastagex_dsstagec2e76d84 b
,datastagex_dsoutputpinc2e76d84 c, datastagex_dslinkc2e76d84 d,
datastagexdsderivationc2e76d84 e
where a.xmeta_repos_object_id_XMETA =b.container_rid and
c.container_rid = b.xmeta_repos_object_id_xmeta and
c.xmeta_repos_object_id_xmeta = d.from_outputpin_xmeta and
d.xmeta_lockingroot_xmeta=e.xmeta_lockingroot_xmeta
Datastage Developer cum ETL designer.