parallel routine for reconciliation purpose

[jpradeep.net]

Hi I am working on Datastage V8.1 parallel Jobs.

Can anybody please provide me a parallel routine for reconcilitation purpose.
The functioality which i need in the routine is

It must compare the count of input records from the source link and count of output records to the target link. If both are equal,It must return 0 and if the count doesnot match,it must return 1 and abort the job.Finally It has to write in the job log that depends on the return code.
i.e If count matches "The input count and output count matched",
if count doesnot match,job must be aborted and "The input and output count doesnot matched" into the log.

please help me in writing this routine.

[ray.wurlod]

If you're going to run this from a Routine activity or as an after-job subroutine (and these are the only sensible places to perform reconciliation) then a parallel routine is not what you want. You need a server routine or, perhaps, a job. Please clarify your requirement.

[jpradeep.net]

ok....Can you tell me the after-job subroutine for the above function which i specified earlier........

[ray.wurlod]

ETLstats, available from Kim Duke's website for free.

[kduke]

These kind of audits do not work. You need reject links on most of the PX stages to capture what was rejected.You need to count what made to the target tables. You need what I call completeness reports. It is included in EtlStats. I wrote a blog and several topics about it.

[marpadga18]

I have routine which was same like your requirement see below you need to specify all Input link,output link,input stage name,output stage name etc etc..i am mentioning all the details below the code

********************************************************************************************************************************************************

ErrorCode = 0 ; * set this to non-zero to stop the stage/job

$INCLUDE DSINCLUDE JOBCONTROL.H

iSpecThreshold = 0

*---- Start processing

strOutPath = DSGetParamInfo(DSJ.ME, "$HCP_UNIX_ROUTINE_FILEPATH", DSJ.PARAMVALUE)
iSpecThreshold = DSGetParamInfo(DSJ.ME, "JPM_EXCP_THRESH", DSJ.PARAMVALUE)

*---- Opening the File

CntFilename = DSGetParamInfo(DSJ.ME, "JPM_ROUTINE_FILENAME", DSJ.PARAMVALUE)
strFileName = strOutPath:"/":CntFilename

*--Call DSLogInfo("Input link":strFileName,DSJ.ME)
OpenSeq strFileName To objFileVar

Else

Create objFileVar

Else

ErrorCode = 1

WeofSeq objFileVar

End

End

WeofSeq objFileVar

*---- Getting the link count for Input link

Call DSLogInfo("Input link",DSJ.ME)
iReadInputRecCount=0
InputStageName1 = DSGetParamInfo(DSJ.ME,"JPM_INPUT_STAGE", DSJ.PARAMVALUE)
InputLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_INPUT_LINK", DSJ.PARAMVALUE)
iReadInputRecCount1 = DSGetLinkInfo(DSJ.ME,InputStageName1,InputLinkName1,DSJ.LINKROWCOUNT)
iReadInputRecCount=iReadInputRecCount1

*----Checking if reject link exists or not

Reject_Status = 0

Reject_Status = DSGetParamInfo(DSJ.ME, "JPM_REJECT_STATUS", DSJ.PARAMVALUE)

If(Reject_Status >0) Then

*---- Getting the link count for Reject link

Call DSLogInfo("Reject link",DSJ.ME)
iReadRejCount = 0
RejectStageName1 = DSGetParamInfo(DSJ.ME,"JPM_REJECT_STAGE1", DSJ.PARAMVALUE)
RejectLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_REJECT_LINK1", DSJ.PARAMVALUE)
iReadRejCount1 = DSGetLinkInfo(DSJ.ME,RejectStageName1,RejectLinkName1,DSJ.LINKROWCOUNT)
Call DSLogInfo("Reject Link1:" : iReadRejCount1,DSJ.ME)

iReadRejCount = iReadRejCount1

End Else

iReadRejCount = 0

End

Call DSLogInfo("--------------JOB STATISTICS-------------",DSJ.ME)

Call DSLogInfo("Input Record Count :":iReadInputRecCount,DSJ.ME)

Call DSLogInfo("Reject Record Count :":iReadRejCount,DSJ.ME)

*---- Getting the required counts from the Output link

ProcessStageName= DSGetParamInfo(DSJ.ME,"JPM_OUTPUT_STAGE", DSJ.PARAMVALUE)
ProcessLinkName = DSGetParamInfo(DSJ.ME, "JPM_OUTPUT_LINK", DSJ.PARAMVALUE)
iProcessRecCount = DSGetLinkInfo(DSJ.ME,ProcessStageName,ProcessLinkName,DSJ.LINKROWCOUNT)

strRecCount = FMT(iProcessRecCount,"10L")

Call DSLogInfo("Output Record Count : " :strRecCount, DSJ.ME)

Call DSLogInfo("Specified Threshold value : ":iSpecThreshold,DSJ.ME)

*---- Check for the Threshold

fthreshold=0

fthreshold= iReadRejCount

Call DSLogInfo("Actual Threshold value:":fthreshold,DSJ.ME)



*---- Check for the Input and output Record Count

iTotalRecordCnt = iProcessRecCount+iReadRejCount

If ((iTotalRecordCnt = iReadInputRecCount) And fthreshold <=iSpecThreshold) Then

fthresholdSts= "SUCCESS"

End Else

fthresholdSts = "FAILURE"

End

*---- Other Parameters

JobName = DSGetJobInfo (DSJ.ME, DSJ.JOBNAME)
JobStartTime = DSGetJobInfo (DSJ.ME,DSJ.JOBSTARTTIMESTAMP)
JobEndTime = DSGetJobInfo (DSJ.ME,DSJ.JOBLASTTIMESTAMP)
ProjectID = DSGetParamInfo(DSJ.ME,"$HCP_SHARP_DS_PROJ", DSJ.PARAMVALUE)
SequenceName = DSGetParamInfo(DSJ.ME,"JPM_DS_SEQ", DSJ.PARAMVALUE)
MetricDescription= DSGetParamInfo(DSJ.ME,"JPM_METRIC_DESC", DSJ.PARAMVALUE)

*---- Wrting the Reonciliation count into a file in format ProjectID,SequenceName,Job name,JobStartTime,JobEndTime,Input Records,Success records,Reject records,MetricValue,MetricDescription,Comments

strReconCount = ProjectID:',':SequenceName:',':JobName:',':FMT(JobStartTime,"20L"):',':FMT(JobEndTime,"20L"):',':FMT(iReadInputRecCount,"10L"):',':FMT(iProcessRecCount,"10L"):',':FMT(iReadRejCount,"10L"):',':fthresholdSts

WriteSeqF strReconCount To objFileVar Then

End

*---- Check for threshold and abort the job

If ((iTotalRecordCnt = iReadInputRecCount) And fthreshold <=iSpecThreshold) Then

Call DSLogInfo("The records matched with the Input and Output and also Actual Threshold value is lesser than or equal to Specified Threshold value",DSJ.ME)

End Else

Call DSLogFatal ("The records did not match with the Input and Output or Actual Threshold value is greater than Threshold value",DSJ.ME)

End

*---- Check for Zero Reject Record count

if iReadRejCount<=0 then

Call DSLogInfo("No 'Reject Records' are present",DSJ.ME)

End



*---- Closing the Report File

[marpadga18]

First copy the code in notepadand and make necessary changes(for YOUR project) In the code.
save it then import to your Routines then open the code and compile it should give
"copile Sucessfully" without any errors
Then go to job properties Select the parameters and create following parameters for your Job
(don't create in admin tool create in your job only)

These are the parameters you need to create in your specific job
Parameter name | Prompt |Type Default value
JPM_INPUT_STAGE | JPM_INPUT_STAGE |String YOUR "INPUTSTAGENAME" in YOUR job
JPM_INPUT_LINK | JPM_INPUT_LINK |String YOUR "INPUTLINK" in YOUR job
JPM_OUTPUT_STAGE | JPM_OUTPUT_STAGE |String YOUR "OUTPUTSTAGENAME" in YOUR job
JPM_OUTPUT_LINK | JPM_OUTPUT_LINK |String YOUR "OUTPUTLINK" in YOUR job
JPM_EXCP_THRESH | EXCEPTION_THRESH |String 0
JPM_REJECT_STATUS | REJECT_STATUS |String 0
JPM_AGGR_STATUS | AGGR_STATUS |String 0
JPM_METRIC_DESC | JPM_METRIC_DESC |String Not Applicable
JPM_DS_SEQ | JPM_DS_SEQ |String YOUR "SEQUENCE NAME"
JPM_INPUT_FILE_NAME | JPM_INPUT_FILE_NAME|String Not Applicable
JPM_ROUTINE_FILENAME |JPM_ROUTINE_FILENAME|String xyz.Routine.txt(give the the name of the file were YOUR info will be created in xyz.txt file and u can see in director log also)



then go to parameters tab in job properties for "After-job subroutine" select the routine which you imported
then check the box Only run after job subroutine on successful job completion
then you can see the record counts in log and xyz.txt file as well

Hope this helps

[ray.wurlod]

Ur was a city in ancient Babylonia. The second person possessive pronoun in English is spelled "your".

DSXchange is not a mobile telephone. There is no requirement for SMS-style abbreviations. Please strive for a professional standard of written English on DSXchange. English is difficult enough for those whose first language is not English, without introducing more variants.

[marpadga18]

OK corrected the errors!!

[ray.wurlod]

Except on JPM_INPUT_LINK !

[chulett]

Thk u!