Capturing row counts from links in ETL job
Moderators: chulett, rschirm, roy
Capturing row counts from links in ETL job
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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().
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Mamu Kim
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
#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