Routine for DSJOBGETINFO

[bobbysridhar]

Hi,
I need to populate an audit table with columns
job start time
job end time
elapsed time
inserted records
updated records
rejected records
I need to have at job level for each job.
I tried different routines like DSJOBGETINFO,DSJOBLINKINFO AND SO ON
But not getting the desired results.
Could somebody please provide me with the routine.

[ray.wurlod]

How about you show us what you tried?

And maybe start by getting the routine names correct. For example, it's DSGetJobInfo(), not DSJobGetInfo().

[ray.wurlod]

Download ETLstats from Kim Duke's website - this package does all that and more. (And it's free.)

[chulett]

As noted, sharing with us what exactly you've tried so far and what in the heck "not getting the desired results" means can help us help put you on the right track.

[bobbysridhar]

Hi,
This is the code to get reject link I wrote.
Could somebody help me in getting the desired result

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE DSINCLUDE DSJ_XFUNCS.H

vActiveStages = 0
i = 0 ; n=0
SrcStageName = 0
SrcStage = 0
ThisJobName = 0
vActiveStageslink = 0
SrcRecCount=0

      ** Check if the job is the current one. In this case do not try to attach but use DSJ.ME

      ThisJobName= DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)
      If ThisJobName<>JobName Then
         JobHandle = DSAttachJob(JobName, DSJ.ERRFATAL)
      End Else
         JobHandle=DSJ.ME
      End

      Result = DSGetJobInfo (JobHandle, DSJ.JOBSTATUS)

vActiveStages = DSGetJobInfo (JobHandle, DSJ.FULLSTAGELIST)

CountOfStages = DCount(vActiveStages,",")

*** Getting the target stage name and the no. of records processed by the link preceding the stage.

***Please note that the target stage name should be standardised.

For n = 1 to CountOfStages
        TgtStage = Field(vActiveStages,",",n)
        UNTIL RIGHT(TgtStage,4) = "REJT"
NEXT n

TgtStageName = TgtStage

TgtStageLnk = DSGetStageInfo (JobHandle, TgtStageName, DSJ.LINKLIST)

TgtRecCount = DSGetLinkInfo (JobHandle, TgtStageName, TgtStageLnk, DSJ.LINKROWCOUNT)

Ans=TgtRecCount

[ArndW]

I'm not at a place where I can check, but I've done this type of log information several times before and it has always been a bit work to get functioning correctly. The code you've posted looks good and you haven't stated which part isn't working - I would guess that everything but the innermost GetLinkInfo() is working as expected, is that the case? You will need to add the calls to get the passive stage information in order to see if you will correctly read the reject link data.

[chulett]

As Arnd notes, which part of this does not seem to be working? What values are you passing in to the routine - JobName? Anything else?

[bobbysridhar]

I have error handling after oci stage. Target stage count gives total number of records. But I want to get inserted records,update records,rejected records seperately. Need to modify my routine to get those records seperately. I would be greatful if someone provide me the modifications.

[ray.wurlod]

Do you have separate links for inserts, updates and deletes? If so you can capture those link row counts separately. If not, there is no solution.

[bobbysridhar]

yes i have seperate links for insert,update,reject records.
i need to have job start time,end time and elapsed time also.

[chulett]

Do they follow a consistent naming convention like you seem to be doing with "REJT" records? If so, you could just expand what you are doing to recognize and classify those as well while you loop through the lists you are building.

You can use the DSGetJobInfo() routine to capture both the DSJ.JOBSTARTTIMESTAMP and DSJ.JOBLASTTIMESTAMP strings. Computing the elapsed time is up to you as far as I know.

[ray.wurlod]

SecondsSinceFromTimestamp() function.