Source to Target mapping

[ray.wurlod]

Maybe it's something simple, like the fact that you left the trailing semi-colon off the SQL query.

Each link has two "pin" entries (a pin is the end of a link, that connects to a stage). Link entries in DS_JOBOBJECTS have an identity of the form VnSmPk where n is the view number (job or container), m is the stage number and k is the pin number.

[eostic]

You mentioned BG in your initial post........do you have Metadata Workbench installed? That would potentially be another way to get the connection details you are looking for...

Ernie

[elavenil]

Thanks for all your responses.

Hi Ernie,

We explored BG option but it did not work out very well due to some internal constraints.

Hi Ray,

Simple source to target mapping in transformer is as mentioned below.

Source columns:
Emp Name (Varchar (30))
Emp Salary Decimal (18,3)

Target Columns:
Emp_Name (Varchar(30))
Emp_No Integer
Emp_Salary Decimal(18,3)

Source to Target Mapping in Transformer is:
Target Column Source Column
Emp_Name Emp Name
Emp_No @INROWNUM
Emp_Salary Emp Salary

What i am trying to achieve here is as below.

Target Column Source Column
Emp_Name Emp Name
Emp_No @INROWNUM
Emp_Salary Emp Salary

Thanks in advance for your help.

Regards
Elavenil

[ray.wurlod]

OK, I'm not on site till Monday, but you're basically looking for those records in DS_JOBOBJECTS that have an OLETYPE that indicates that they are output links from Transformer stage.

Without access to DataStage I can't tell you which field in the record contains the column names and which contains the output derivation expressions, but they're in there. Is that all you need, or would you be needful of the output constraint also?

[elavenil]

Thanks Ray for your quick response on this.

I will explore OLETYPE & other columns in DSOBJECTS and let you know.

Kindly let me know more detail when you get sometime to explore this on next week.

Regards
Elavenil

[MarkB]

Being that a DSX file is a text file, you could export the DSX and an awk program could be written to do what you want.

[eostic]

I've had similar success using DataStage to read the xml variant of a .dsx file.

Ernie

[ray.wurlod]

The output pin from a Transformer stage has an OLETYPE of CTrxOutput in its DS_JOBOBJECTS record.

Within this record, multivalued field numbers 14-34 describe the columns on the link and, specifically, the derivation expressions are in field number 25.

To see just this, try something like

Code: Select all

SELECT EVAL "@RECORD<14>" AS COLUMN_NAME FMT '26L', EVAL "@RECORD<25>" AS DERIVATIONS FMT '40L' FROM DS_JOBOBJECTS WHERE OLETYPE = 'CTrxOutput' AND OBJIDNO = (SELECT JOBNO FROM DS_JOBS WHERE NAME = '<<Job name>>');

[elavenil]

Thanks Ray for your help.

The provided query returns the result it was expected.

Note: I am trying to write the results into a file.

Regards
Elavenil

[ray.wurlod]

Search here or UniVerse manuals for COMO and DIVERT.OUT commands.

Code: Select all

COMO ON derivations.txt
SELECT .... ;

Your output will be in ./\&COMO\&/derivations.txt

Or use SETPTR command mode 3 to direct output to a hold file when the LPTR keyword is used.

Code: Select all

SETPTR 1,132,20000,0,0,3,BANNER derivations.txt
SELECT ..... LPTR 1;

Your output will be in ./\&HOLD\&/derivations.txt