Source to Target mapping

elavenil · Post by **elavenil** » Thu Mar 07, 2013 3:54 am

Hi DS Gurus,

I would like to get expert advice on this.

The job design is as below.

DS --> Transformer --> Teradata Load

What i am trying to do is to list all target fields and assigned column mapping or derivation.

I hope the above info can be extracted from Universe tables and could you enlighten some light on this. How do i extract target columns and associated mapping for a tranformer?

Another thought is to load DataStage job into Business Glosssary and extract the required info. Could you let me know if this is possible as well.

Thanks in advance for your inputs & help.

Regards
Elavenil

prasannakumarkk · Post by **prasannakumarkk** » Thu Mar 07, 2013 4:26 am

You can export the job as XML. Load the XML in to excel as XML table. By filtering out proper values in certain columns of excel like output link name of transfomer. You will get what is needed.

Export the job as XML and load in to excel, then you will get an idea what am trying to convey.

elavenil · Post by **elavenil** » Mon Mar 11, 2013 4:37 am

Hi Prasanna,

Thanks for your response.

We would like to avoid any manual process on getting the source to target mapping. That is the reason, we would like to use Universe repository to get the required mapping.

Appreciate it if could someone enlighten on this.

Regards
Elavenil.

prasannakumarkk · Post by **prasannakumarkk** » Mon Mar 11, 2013 4:57 am

Automating with universe repository with the exploration i did, was not a reusable solution since the table names are not unique. And in the higher version of DS you may have it in different database.
If you want to automate this, you can very well export the jobs with a command and use DOM parser in java to get you the result.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 11, 2013 1:31 pm

The derivations and target columns are to be found in the DS_JOBOBJECTS record for the output link from a Transformer stage.

To determine that a link is an output link from a Transformer stage you will need to determine its source stage repository identifier (for example V0S7) and query DS_JOBOBJECTS to verify whether that stage is a Transformer stage.

elavenil · Post by **elavenil** » Mon Mar 11, 2013 8:02 pm

Thanks Ray and Prasanna for your response.

Hi Ray,

I will explore the DS_JOBOBJECTS table to get source to target mapping from transformer stage and let you all know the outcome.

Regards
Elavenil

elavenil · Post by **elavenil** » Wed Mar 13, 2013 3:17 am

Hi Ray,

Created a simple job, which looks like mentioned below.

Row Generator --> Copy --> Dataset.

When DS_JOBS is queried, i can see this job but when i executed a query on DS_JOBOBJECTS, only 0 rows are returned.

Can you enlighten why there is no record in DS_JOBOBJECTS though the job exists?

In addition to that, could you help providing a query to list out target columns and its corresponding derivations from DS_JOBOBJECTS.

Thanks
Elavenil

ray.wurlod · Post by **ray.wurlod** » Wed Mar 13, 2013 3:28 am

How did you query DS_JOBOBJECTS ? What information were you seeking to retrieve? Do you understand the key structure of DS_JOBOBJECTS?

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 13, 2013 4:19 am

Ray do you have the data model of Xmeta?

elavenil · Post by **elavenil** » Wed Mar 13, 2013 8:44 am

Hi Ray,

Just executed LIST.DICT DS_JOBOBJECTS and i was able to get the metadata of this table. And executed "Select count(*) from DS_JOBOBJECTS" and it returned 0 rows.

I would like to get output columns and its derivations / input columns for the output link of transformer.

Is there any document, which provides the structure of these tables?

Thanks in advance for your help.

Regards
Elavenil

chulett · Post by **chulett** » Wed Mar 13, 2013 9:24 am

No there isn't... not for the 'legacy' repository nor XMETA.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 13, 2013 1:14 pm

What version are you on? I am very surprised to learn that DS_JOBOBJECTS is empty. I will check my 8.7 system when I next get access to it.

elavenil · Post by **elavenil** » Wed Mar 13, 2013 8:11 pm

I am using 8.7 version of DS. Yes, it is very strange that DS_JOBSOBJECTS is empty.

Appreciate your help on this.

Thanks & Regards
Elavenil

ray.wurlod · Post by **ray.wurlod** » Thu Mar 14, 2013 12:07 am

My DS_JOBOBJECTS table has over 1000 records in it, in a project with only 30 jobs. There are two entries per job, one entry per container, one entry per stage, two entries per link, and one entry per annotation.

We are running version 8.7.0.1. That is, Fix Pack 1 is installed.

Can you try this query? Note that it is not SQL, it does not have a trailing semi-colon.

Code: Select all

SELECT DS_JOBOBJECTS TO 9

Let us know its output.

elavenil · Post by **elavenil** » Thu Mar 14, 2013 12:49 am

Hi Ray,

Thanks for your details on DS_JOBOBJECTS.

The query output is 36 record(s) selected to SELECT list #9.

I am curious to understand why SELECT COUNT(*) FROM DS_JOBOBJECTS returned no rows.

How do i filter these rows for a single link for eg. an output link?

That would greatly help achieving the things i require.

Regards
Elavenil