DSXchange

Posted: **Sun Jan 30, 2005 10:04 pm**

Hi,
I have requirement where after completion of First Job, which loads into a table from a flat file, I am starting a Job for Audting. In my Auditing job i want to store the "No of records read","No of records loaded" of my first job. But I dont know how to read Director log and filter out only required part. Is there any simple way for that?

Posted: **Sun Jan 30, 2005 10:20 pm**

Either use the DS APIs for getting link statistics (read your DS BASIC manual, search DSXchange for examples) or use the dsjob command line to call the APIs for you.

Either way, you'll need an after-job routine call, to either your custom subroutine or to ExecDOS to run a batch file of dsjob commands.

Posted: **Sun Jan 30, 2005 11:30 pm**

Thanks, But I know i can do it using API or after/before job routine, but i want the value inside my transformer and I dont want to create text file prior to running the job and then read the text file in my transformer stage. I want something that would give me the value, on the fly.

Posted: **Sun Jan 30, 2005 11:44 pm**

You can never really know the number of records loaded in a Transformer stage; only the number of records sent to the data target. The remainder depends on too many factors to post here.

Why do you want to do it this way? The counts are in variables @INROWNUM and @OUTROWNUM in the Transformer stage; why do you want to interrogate the log?

Incidentally, the row counts are not written to the log until the Transformer stage has processed its last row and is closing, so that trying to obtain this information from the log from within the Transformer will not succeed in any case.

And opening, reading and closing a text file and interrogating the job log for every row processed will slow your throughput immensely.

Finally, the job log is not a text file; it's a database table.

Posted: **Mon Jan 31, 2005 12:01 am**

Thanks Guys,
I got solution to it.

Posted: **Mon Jan 31, 2005 12:04 am**

This is both a caring and a sharing place. How did you solve it? Help out others who might be searching for this solution in the future!

Posted: **Mon Jan 31, 2005 12:10 am**

Hey Ray
U didnt read my requirement properly. I wanted my second job to read about my first job statistics, from the director log, in one of its Transformer stage and store the information in the Audit Table.

And Using after/before job routine in first/second job would mean creating some physical file (say text file) and then reading that file in one of the transformer stages of my second Job.
Which I dont want.
Thanks any way, I got the solution myself.

Posted: **Mon Jan 31, 2005 12:38 am**

Here is the Solution, Create a routine and pass an argument say,Arg1.
-----------------------------------------------------------------------------
Equate RoutineName To "GetLinkCount"

Call DSExecute("DOS",Arg1,Output, SystemReturnCode)
Count =Output[':',2,1]
Ans=Oconv(Count, "MCN")
Return(Ans)
----------------------------------------------------------------------------
while calling routine, Arg1=C:\Ascential\DataStage\Engine\bin\dsjob -server <server> -user <user> -password <password> -linkinfo <Project> <JobName> <Stagename> <LinkName>

I would suggest you parameterize the value assigned to Arg1

Posted: **Mon Jan 31, 2005 1:22 am**

You could avoid the overheads of opening an operating system shell and of starting up the dsjob command by using the DataStage BASIC function DSGetLinkInfo() directly in your BASIC code. You would, of course, need to attach and detach the finished job, but this is a smaller overhead than your approach. So, for example:

Code: Select all

FUNCTION GetLinkCount(JobName, StageName, LinkName)
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF

hJob = DSAttachJob((JobName), DSJ.ERRNONE)
Name = DSGetJobInfo(hJob, DSJ.JOBNAME)
If Name Matches "'-'1N0N"
Then
   Ans = "Failed to attach job " : Quote(JobName)
End
Else
   Temp = DSLinkInfo(hJob, StageName, LinkName, DSJ.LINKROWCOUNT)
   Begin Case
      Case Temp = DSJE.NOERROR
         Ans = Temp
      Case Temp = DSJE.BADSTAGE
         Ans = "Stage name " : Quote(StageName) : " not found in job."
      Case Temp = DSJE.BADLINK
         Ans = "Link name " : Quote(LinkName) : " not found in job."
      Case @TRUE
         Ans = "Error encountered in DSGetLinkInfo.  Code " : Temp
   End Case
   ErrCode = DSDetachJob(hJob)
End
RETURN(Ans)

Edited to correct small typo (see next post).

Posted: **Mon Jan 31, 2005 2:09 am**

hey ray,
Thanks, a small correction to your code.
It would be "DSGetLinkInfo" NOT "DSLinkRowCount".
And in my requirement, I need to get count of different link of my first job in one of the transformer stage of my second job. So if I do Attaching of my first job, to get One count, at the same time I WONT be able to attach that job to get other counts. You know that why?
So I think in my case, the first way is suitable. Well I do agree with your thoughts.

Posted: **Mon Jan 31, 2005 4:03 am**

Do you really want to do this in a Transfomer stage, so that you get the (same) link count from the first job for every row processed in your second job? (Or are you perhaps initializing a stage variable, in which case I withdraw the question?)

You can get as many row counts as you like in a single function call (and therefore a single attach/detach). Pack them into a delimited string for return, and unpack them in the caller. All you have to do is to provide the different stagename/linkname combinations. This, too, could be a delimited string.

Posted: **Mon Jan 31, 2005 5:33 am**

thanks ray, My requirement is like that.

I still have a problem, either of the way doesnt give desired result when the job status is other than 'finished'.

So thinking of some other way, do let me know if anyone has some other idea's.

Posted: **Mon Jan 31, 2005 3:57 pm**

What would you expect to get if the job status is other than finished?
Think about what the other possibilities are: still running, aborted, finished with warnings, crashed, stopped (by operator).

DSXchange

Read Log

Read Log