Converting Link Column Values to a String

[ray.wurlod]

You do not have documented access to the column names from DataStage BASIC.
It is easy enough to create a list of the values in each column, but pairing that with column names requires access to the Repository table containing run time metadata for the job.
How to do this is undocumented, and may change over time. To make matters worse the Repository table in question (a) does not use first normal form (so that the column names, types, etc., are stored as a "nested table" or collection), and (b) has a varying row structure (because it can!) which means that you would have to know exactly what you are doing.
If it were going to be anywhere, it would be returned by DSGetLinkInfo (since the collection of columns is a property of a link), but it simply ain't there.
One possibility would be to use BASIC SQL Client Interface (BCI) functions to interrogate the table itself (SQLColumns() function), but how do you then match up with what's in the DataStage job itself, which may not be processing all columns from the table?

[ray.wurlod]

The column values are exactly what you are dealing with in DataStage.

If you are in a Transformer stage, you can use the input column names directly in an output column derivation.
For example:
"(":InputLink.UserNo : ")(" : InputLink.LastName : ")(" : InputLink.FirstName : ")"

If you are in a Routine, such as a transform function, you can pass the column values to the routine as arguments, or assemble them into a single string and pass that (something like the example above) as an argument.

There's no documented way to obtain the column values directly from the job itself, though you may like to peruse the code generated by a Transformer stage (in a directory called RT_BPnnn, where nnn is a job number) to see how DataStage actually does it. Your problem in trying to use this method is that an external function does not have access to the local variables and constants in the Transformer stage. You would have to reproduce this work.

The function you have in mind is do-able, but who's going to pay for the two to five days needed to develop and test it?

-----Original Message-----
From: bhala@bhala.com [mailto:bhala@bhala.com]
Sent: Friday, 11 October 2002 12:04 PM
To: Wurlod, Ray (RTPeP)
Subject: Sent From Tools4DataStage by bhala


Hello Ray.Wurlod

You received the following message from : bhala (bhala@bhala.com)

At: http://www.tools4datastage.com/forum/

Actually would like to know how to get just column values w/o the names too. What is an easy way to do that.

[ray.wurlod]

Here's a function to return the column names associated with a link.

FUNCTION GetLinkColumnNames(JobName, StageName, LinkName)

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
DEFFUN GetJobNumber(Name, Text) Calling "DSR_GETJOB"


Ans = DSJE.BADVALUE

If UnAssigned(JobName) Or IsNull(JobName) Then GoTo MainExit
If UnAssigned(StageName) Or IsNull(StageName) Then GoTo MainExit
If UnAssigned(LinkName) Or IsNull(LinkName) Then GoTo MainExit


JobNumber = GetJobNumber(JobName, ErrorText)
If ErrorText <> ""
Then
Ans = DSJE.BADHANDLE
GoTo MainExit
End


Ans = ""

ConfigFileName = Convert(" ", "", "RT_CONFIG" : JobNumber)
Open ConfigFileName To ConfigFileFvar
Then
JobID = JobName
ReadV JobKey From ConfigFileFvar, JobID, 0
Then
StageID = JobName : "." : StageName
ReadV StageKey From ConfigFileFvar, StageID, 0
Then
LinkID = JobName : "." : StageName : "." : LinkName
ReadV ColumnList From ConfigFileFvar, LinkID, 21
Then
Ans = Raise(ColumnList)
End
Else
Ans = DSJE.BADLINK
End ; * end of ReadV statement for Link
End
Else
Ans = DSJE.BADSTAGE
End ; * end of ReadV statement for Stage
End
Else
Ans = DSJE.BADHANDLE
End ; * end of ReadV statement for Job
End ; * end of Open statement



MainExit:

RETURN(Ans)

[ray.wurlod]

It WAS formatted when I sent it; "they" removed the leading spaces!
Paste the code into your Routine definition, and click on the Format button and all will be beautiful again.