Way to Retrieve the Transformations of a Job

sam paul · Post by **sam paul** » Thu Aug 06, 2015 4:28 am

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!!!

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2015 4:38 am

use metadata workbench or try investigating xmeta.

rkashyap · Post by **rkashyap** » Thu Aug 06, 2015 6:08 am

Another option - leverage InfoSphere DataStage job reporting.

chulett · Post by **chulett** » Thu Aug 06, 2015 10:55 am

Isn't there still a "Job Report" that you can generate from the Designer via a button? Not anything to do with operational metadata, but simply an HTML report of the contents of the job (transforms and all) all nicely hot-linked.

qt_ky · Post by **qt_ky** » Thu Aug 06, 2015 12:58 pm

You bet!

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 06, 2015 4:02 pm

Yeah, completely forgot about that. dsdesign.exe can be used to automate that as well. Depends on the requirement though.

sam paul · Post by **sam paul** » Fri Aug 07, 2015 12:47 am

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.

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>'

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!!

ray.wurlod · Post by **ray.wurlod** » Fri Aug 07, 2015 5:27 am

If you have Metadata Workbench you can request a data lineage report that will give you the transformations.

rkashyap · Post by **rkashyap** » Fri Aug 07, 2015 10:34 pm

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.

sam paul · Post by **sam paul** » Mon Aug 10, 2015 12:09 am

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.

Code: Select all

datastagex_dsjobdefc2e76d84 a, datastagex_dsstagec2e76d84 b,datastagex_dsoutputpinc2e76d84 c, datastagex_dslinkc2e76d84 d

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!!!

sam paul · Post by **sam paul** » Mon Aug 10, 2015 12:12 am

ray.wurlod wrote:If you have Metadata Workbench you can request a data lineage report that will give you the transformations. ...

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.

chulett · Post by **chulett** » Mon Aug 10, 2015 7:17 am

Well... that is where it all starts.

rkashyap · Post by **rkashyap** » Mon Aug 10, 2015 8:06 am

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

What is the query that you are using to join these tables?

ray.wurlod · Post by **ray.wurlod** » Mon Aug 10, 2015 4:22 pm

Metadata Workbench does not require that you have a sequence. It does, however, require that you've used, or converted to, shared table definitions.

sam paul · Post by **sam paul** » Wed Aug 12, 2015 3:45 am

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