Counting the number of rows extracted

[Ragunathan Gunasekaran]

Hi ,
I would like to validate that the number of rows extracted from the oracle stage to the hashed file.
If the validation fails then the log should say a user defined message that would be hard coded

Any help on this please ?

[JoshGeorge]

Dsloginfo is used to log your comments to the log file. Now if you can be more specific on what you want it will be easy to answer. As per heading you want to count the number of rows extracted on a link or you want to log a user defined message to the log everytime a row fails the validation you are doing inside the job?

[rafik2k]

Create one after job subroutine

use the basic function to get rowcount passing through the each links.

Result=

Code: Select all

DSGetLinkInfo (JobHandle, StageName, LinkName, InfoType)

, Give InfoType as DSJ.LINKROWCOUNT

Compare the result and call DSLogInfo to log the custom message.

[Ragunathan Gunasekaran]

When the job is finished with the extract operation and load operation . I would like to validate the load by checking the count of the rows loaded against the no of rows in the source .If the count is not matching then a warning should be posted in the log which would be a user defined message .
assume that there is just one source and target

[Ragunathan Gunasekaran]

I have not worked around in creating subroutines and i dont find an option to create a new after job subroutine in the Job properties page . There are only predefined routines available How should i create a subroutine ?

[rafik2k]

Create a server routine,
specify its type as Before/After subroutine from drop down menu.


once you compile it, it will be available after job subroutine in the Job properties page

[Ragunathan Gunasekaran]

Compiling: Source = 'DSU_BP/DSU.ValidateLoad', Object = 'DSU_BP.O/DSU.ValidateLoad'
?
0004 int ReadCount:=0,WriteCount :=0;

^
Variable Name (UNDEFINED) unexpected, Was expecting: Assignment Operator
0005 ReadCount:=DSGetLinkInfo (DSJ.ME, "LogInfo_tf", "Read_Rows_lk", DSJ.LINKROWCOUNT);

^
',' unexpected, Was expecting: '!', ')', '=', "AND", "OR", "LT", "LE",
"GT", "GE", "NE", "EQ", "MATCH"
0006 WriteCount:=DSGetLinkInfo (DSJ.ME, "LogInfo_tf", "Write_Rows_lk", DSJ.LINKROWCOUNT);

^
',' unexpected, Was expecting: '!', ')', '=', "AND", "OR", "LT", "LE",
"GT", "GE", "NE", "EQ", "MATCH"
0007 if ReadCount ==WriteCount

^
'=' unexpected, Was expecting: '!', '=', "AND", "OR", "LT", "LE", "GT",
"GE", "NE", "EQ", "MATCH"
0008 0;

^
"ELSEEOL" unexpected, Was expecting: ';', End of Line
Array 'DSGetLinkInfo' never dimensioned.
WARNING: Variable 'DSJ.ME' never assigned a value.

6 Errors detected, No Object Code Produced.

[chulett]

Post your code wrapped in

Code: Select all

 tags.[/b] Explain what you built. Can't help otherwise.

[JoshGeorge]

Below is a format:

You need to pass all the parameters required for this routine:

extractJobName, loadJobName, StageName, LinkName, StageName2, LinkName2 ...

And make sure StageNames and LinkNames are the final ones used in respective jobs.

If used as after/before routine, you will have to pass all the above specified parameters as one and segregate in the routine.
===============================================
$INCLUDE DSINCLUDE JOBCONTROL.H
Ans = 0
handleJob = DSAttachJob(extractJobName, DSJ.ERRNONE)
If NOT(handleJob) Then
Ans=-99
End
Else
ExtractRowCount = DSGetLinkInfo (handleJob, StageName, LinkName, DSJ.LINKROWCOUNT)
End
DetachResult = DSDetachJob(handleJob)

handleJob = DSAttachJob(loadJobName, DSJ.ERRNONE)
If NOT(handleJob) Then
Ans=-99
End
Else
LoadRowCount = DSGetLinkInfo (handleJob, StageName2, LinkName2, DSJ.LINKROWCOUNT)
End
DetachResult = DSDetachJob(handleJob)

If LoadRowCount <> ExtractRowCount then
Call DSLogInfo("Your Msg", "Job_Control") End
Else
Call DSLogInfo("Your Msg", "Job_Control") End
Ans =0
=========================================
There are a lot more to this routine, this should be treated as just a format.

[Ragunathan Gunasekaran]

Hi ,George
Happy to say that I have compiled the routine without any errors.since we got the required counts in the subroutine itself there was not anything passed into the routine . I have set the job properties to point to the after job subroutine . Let me run the job and share the joy with you

Thanks a lot [/url]

[Ragunathan Gunasekaran]

The Job is still running. I have attached the log detail from the director.I have put the same user defined message for both the conditions but the message have not turned up in the log .

Kindly help


TestLogWrite_job..LogInfo_tf: DSD.StageRun Active stage finishing.
2000 rows read from Read_Rows_lk
2000 rows written to Write_Rows_lk
0.280 CPU seconds used, 4.106 seconds elapsed.

[chulett]

A little premature to mark this as resolved. Compiled ok != runs ok or even solves the problem. I'd wager you'll need to kill the job.

You are going to have problems attaching to the currently running job. It should just use DSJ.ME for the handle rather than attaching. And will link counts really solve your problem or are you going to need to count the number of records actually in the hashed file after the job completes?

[ray.wurlod]

The number of rows sent along the link is not necessarily the number of rows that end up in the hashed file, because duplicate key values effect a destructive overwrite.