Source and Target mapping data

Titto · Post by **Titto** » Tue Oct 11, 2005 6:50 am

Hi,

I have a requirement, where i need to find out the Source Table column names to Target column names. We are using ODBC stage to connect Oracle database, used user defined queries to join multiple tables to get the data and writing it to a sequential file. Now I have a task to get the Source table name, Source column name mapped with destination column name .. Is there a easy way to get what i needed, or do i need to open the job , SQL and manually map with target file columns.
Pls, any ideas, approaches !!

thanks in advance!

roy · Post by **roy** » Tue Oct 11, 2005 8:06 am

Hi,
In a perferct world your requirement would have been prepared before you actually designed your job.
Since were not living in a perfect wold (at least it seems that way) you will need to do it manually or by building reports.
Such reports might be achieved by utilising the html reports you can generate or perhaps even via Kim's etlstats and such tools he built and you can take a peek at he's site.
Also take a look here

IHTH,

Titto · Post by **Titto** » Tue Oct 11, 2005 11:03 am

Thanks roy! you are correct we need do the mapping before developing the code. But the requirement is now to trace back..

I generated a .XML file it has the souce and target column names. Can any one help me out with sample how to use XML stage to parse the .XML input file to Sequential file.
I tried as follows but it has lot of parameters, where do i specify the Input file name.. any help is appreciated!

XML INput stage ------> XML Transformer ------> Seq

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Oct 11, 2005 3:12 pm

Why not just use a lineage analysis? (You need MetaStage for this, but that's now being pushed as the reporting tool of choice, so you may as well buy now - resistance is useless!!)

kduke · Post by **kduke** » Tue Oct 11, 2005 6:30 pm

Thanks Roy. DwNav will allow you to trace from link to link and job to job.

vmcburney · Post by **vmcburney** » Tue Oct 11, 2005 7:19 pm

One of my personal bugbears is transformation within user-defined SQL. Have a look at it in detail, find out if it renames columns or runs functions on columns. This type of manipulation occurs outside of your DataStage job (on the RDBMS) and can be hidden from some data lineage reporting.

To turn an XML file into a sequential file just import the XML definition into the Manager tool and link your XML Input to a transformer and sequential file output stage. You shouldn't need an XML transform stage.

Then again why turn it into a sequential file? You can view XML in a browser and write your own XSL stylesheets to format it for viewing. There are some sample XSL stylesheets in the DataStage client directory that are designed to format DataStage export XML files.

Titto · Post by **Titto** » Wed Oct 12, 2005 2:49 pm

To turn an XML file into a sequential file just import the XML definition into the Manager tool and link your XML Input to a transformer and sequential file output stage. You shouldn't need an XML transform stage.

As you mentioned Yes! there are lot of transformations from source to Destinations, column names been changed.

Could you please tell me ' Do I need to use XML Input stage then to regular Transformer and to Seq output is it?

XML Input Stage =====> XFRM =====> SEQ.

thanks

vmcburney · Post by **vmcburney** » Wed Oct 12, 2005 7:22 pm

Yes, that will do it. The XML input stage will "flatten" your XML data into a single record type which can easily be handled by a normal transformer.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 13, 2005 4:25 am

MetaStage lineage analysis will track through the name changes.