Counting the number of rows extracted
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
Counting the number of rows extracted
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 ?
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 ?
Regards
Ragu
Ragu
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
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?
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Create one after job subroutine
use the basic function to get rowcount passing through the each links.
Result=, Give InfoType as DSJ.LINKROWCOUNT
Compare the result and call DSLogInfo to log the custom message.
use the basic function to get rowcount passing through the each links.
Result=
Code: Select all
DSGetLinkInfo (JobHandle, StageName, LinkName, InfoType)
Compare the result and call DSLogInfo to log the custom message.
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
Counting the number of rows extracted
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
assume that there is just one source and target
Regards
Ragu
Ragu
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
Creation of after job subroutine
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 ?
Regards
Ragu
Ragu
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
getting error
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.
?
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.
Regards
Ragu
Ragu
Post your code wrapped in
Code: Select all
tags.[/b] Explain what you built. Can't help otherwise.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
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.
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.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
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]
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]
Regards
Ragu
Ragu
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
Job still running and log dint turn up
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.
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.
Regards
Ragu
Ragu
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.