number of rows processed by a link

luca · Post by **luca** » Thu Feb 06, 2003 5:51 am

Hello !
I need to store the number of rows processed by a link in a job server.
How can I add this into my job design ?
I need this number to be store in a table or in a file and to be send be email.
Thanks for your suggestions.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 06, 2003 6:18 am

Interrogate the link row count with DSGetLinkInfo() function. Check it out in the developer's help. Use the function in either job control code (in a controlling job/sequence) or in an after-stage or after-job routine.
The same routine can store the value and call DSSendMail() to send the email.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

luca · Post by **luca** » Fri Feb 07, 2003 1:20 am

Thanks for your help Ray, I'm on the way, by creating a new before/after stage subroutine to do it.

It's the first time I create a subroutine and I will need some help again.

Can someone please tell me if this is OK :

the subroutine must have only 2 arguments.
I have to pass the job name, stage name and limk name in the first argument of the subroutine. I tried to do this in giving this 3, separeted by a semicolon. And then refind the 3 names in the subroutine. Unfortunately, this doesn't work.

Can you tell me how I should pass the parameters to the subroutine ?
Which DS variable should I use to get the job name and the stage name ?
I guess I will have to write the link name (cannot use DS variable for it) ?

Thanks for your help.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 07, 2003 3:02 am

When you create your Routine, make sure its type is "before/after subroutine", not "transform function". Among other things this means that you will have two arguments, called InputArg and ErrorCode.

Assuming it's being run from the job whose link information you are seeking, you can use the special constant DSJ.ME for the job handle and for the stage name.

In the Transformer stage, go to the stage properties and select the name of your (compiled) Routine from the drop down list of before/after subroutine names. In the Input Value field, provide the name of the link. This will be available as InputArg in your subroutine.

Within your subroutine, therefore, your invocation of the function will appear as follows:

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
LinkName = InputArg
RowCount = DSGetLinkInfo(DSJ.ME, DSJ.ME, LinkName, DSJ.LINKROWCOUNT)

The three lines beginning with $ can appear at the beginning of your source code; they ensure that the DSJ.ME constant is properly declared.
Copy the input argument into a local variable to avoid side effects that might be caused by changing the value of InputArg (arguments are passed by reference).
Finally, don't change the value of ErrorCode; any non-zero value will cause your job to abort. (This is its function.)

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

luca · Post by **luca** » Fri Feb 07, 2003 4:35 am

I changed a few things and my code now matches your suggestions.
But the result still bad :
After the DSGetLinkInfo, RowCount values -9 ...

For the link name I pass as a parameter for my subroutine, do I have to enclosed it with "" (ie write : "MyLink" or MyLink ? )
I tried both way, but got the same error in both cases.

Waiting for new suggestions.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 07, 2003 5:30 am

If you inspect JOBCONTROL.H in the DSINCLUDE subdirectory, you will see that -9 means (DSJE.BADLINK) bad link name. That is, the link name you provided is not a known link for the stage. Make sure that the spelling and casing of the link name is the same as in the job design and that the after-stage subroutine is run in the stage to which the link is attached. Check out the online help for the DSGetLinkInfo function.
You should not need to provide quotes around the link name in the Input Value field. It will be passed as a string value. You can confirm this by including a DSLogInfo call in your routine to report the value delivered through InputArg.
Can you advise how many rows were processed on the link, by looking at the "active stage finishing" message in the job log?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

luca · Post by **luca** » Fri Feb 07, 2003 5:43 am

Thanks again.
Yes, I can see the number of rows in the director log.
I checked I used the good link name (I copied my link name and paste it in the Input Value field, but still get the same link name error).
Idea for what else to do ?

ray.wurlod · Post by **ray.wurlod** » Fri Feb 07, 2003 3:31 pm

Please verify that you have checked everything in my previous post, including especially that you are using the after-stage routine in the stage to which the link connects.
You might try hard-coding the stage name and link name in the routine, instead of using DSJ.ME (even though the help for DSGetLinkInfo says you can), to see whether that makes any difference.
What version of DataStage are you using, and on what operating system?

luca · Post by **luca** » Fri Feb 07, 2003 11:31 pm

Yes, I checked everything (espacially before/after subroutine, which I use in the transform stage I want the number of rows in input).
As you suggested, I tried hard-coding job name, stage name and link name, but this is worst as this leads to a -1 error (Invalid JobHandle). The job handle was OK using the DSJ.ME constant (using DSJ.ME works fine), the problem was met later, for link name only : -9 error : LinkName does not refer to a known link of the stage).

version of DataStage : 5.2.1
operating system : UNIX sun solaris 8

Thanks for your help.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 08, 2003 12:32 am

You can't hard code a job NAME, because the function expects a job HANDLE (refer to the help). Just hard code the stage name and the link name, and see what happens.
This may seem radical, but you might also try renaming the Transformer stage and the link in question, just in case the names recorded by DataStage aren't exactly what's displayed on the canvas.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

luca · Post by **luca** » Sat Feb 08, 2003 1:37 am

Hard-coding the stage name and link name works (I got my number of rows doing it).
Hard-coding only the link name and using DSJ.ME for the stage name doesn't work.

So I tried passing both the stage name and the link name separated by a comma and I retrieve this 2 args in my subroutine using the Field function. THIS WORKED.

but still I'm not really happy with this solution because in case someone rename the stage or link name without changing it in the subroutine input value this will not work anymore even if this will still compile.

What do you think about it, Ray ?
Anyway I'll use it since I have no better way to do it.

Thanks a lot.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 08, 2003 6:30 pm

DSJ.ME for the stage name should work, according to the documentation. If it does not, and you can provide a reproducible test case, report a bug to Ascential (via wherever you get your DataStage support).
The whole point of allowing DSJ.ME to be used for stage name (and link name if appropriate - see help for DSGetLinkInfo) is precisely to insulate your code against the change of name you are worrying about.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

bigpoppa · Post by **bigpoppa** » Tue May 13, 2003 7:52 am

I was having the same problem as Luca, in using DSJ.ME for the current stage name. It just wasn't working, even though the Ascl BASIC guide says it should. Anywho, I wrote a similar routine to capture the # rows going into and out of a transform stage. I *thought* I could use the routine in the 'before stage subroutine' to get the number of rows on the inbound link. However, the routine kept reporting the # of inbound rows as 0. I had to then move the query of the inbound rows to the 'after stage subroutine'. So, I ended up writing a routine that
1. Takes in a comma-delimited parameter of the form "filename,stagename,LinkName1,LinkName2,...,LinkNameN"
2. Writes out to an existing file the row counts for every Link queried.

The code is below, in case anyone needs it. There's no error handling. [:D]

$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF

fileCommaInd = INDEX(InputArg,',',1)
FILENM = InputArg[1, fileCommaInd-1]

argLen = len(InputArg)
tempArg = InputArg[fileCommaInd+1, argLen-fileCommaInd]
argLen = len(tempArg)
stageCommaInd = index(tempArg,',',1)
STAGENM = tempArg[1, stageCommaInd-1]

tempArg = tempArg[stageCommaInd+1, argLen-stageCommaInd]
argLen = len(tempArg)
numLinks = count(tempArg, ',') + 1
index = 1
printString = ""

LOOP
WHILE index