Capturing row counts from links in ETL job

[klarson88]

We have DataStage jobs which run after the ETL stages in a sequence that compare counts on the source and target. These require re-connecting to the source and target via ODBC/OCI to query the info. Is there a command to capture the counts from the Extract and Load stages (one is ODBC, the other OCI) to compare instead of re-connecting to the databases to do this step? I can see the info on a job Notification email that I need, but haven't come across a command to capture that info into a variable.

[rgandra]

You can use DSAttachJob,DSGetLinkInfo job control routines. you will find their description in the server job documentation.

-Sandeep

[ray.wurlod]

All row counts are reported in "active stage finishing" messages in the job log. You can view these in Director, or access them programmatically using functions such as DSGetLogSummary(), DSGetNewestLogId() and DSGetLogEntry().

[kduke]

Do a search there is lots of code posted showing how to use these routines. As far as getting row counts from source tables then create a job that

select count(*) from #TableName#

Put the result in a hash file with the parameter TableName as the key. Use UtilityHashRead or whatever the SDK routine name is and it will return this value.

[klarson88]

I was able to write a routine to do what we wanted, using examples from other posts. I know that you can get job names and status directly within the sequencer, but couldn't come up with another way to do this without a routine. Included code below. Thanks all.

#INCLUDE DSINCLUDE JOBCONTROL.H

JobHandle = DSAttachJob(JobName, DSJ.ERRFATAL)

RecFile = DSGetLinkInfo(JobHandle, StageName, LinkName, DSJ.LINKROWCOUNT)

Call DSLogInfo("Output Is: ":RecFile,"GetLinkCount")

IgnoreMe = DSDetachJob(JobHandle)

Ans=RecFile

[kumar_s]

Hi,
In other way, you can try using the commands like dsjob -linkinfo or -logdetail from unix by passing the project name and job name.

-Kumar

[kduke]

dsjob -report is also useful.