ETL THROUGH DATASTAGE

1.Verb "UNLOCK" is not in your VOC

Solution:-

First log in into administrator and go to execute command option of specific project. Than follow below process.

Write “SET.FILE UV VOC UV.VOC” click in execute then write “COPY FROM UV.VOC TO VOC UNLOCK” and after you have to write “UNLOCK USER number ALL”

NB:Next time onward you don’t need to write the command. May be you may need admin ID for this.


2. DataStage Routine to validate number of records as input with total number of output records.
Below example is work for single input sources with target as output link,ignore link and reject link. You can also manage multiple number of link in a job by little bit modifying below routine.

Solution:-

$INCLUDE DSINCLUDE JOBCONTROL.H

FLAG=0
RjctRowCount=0
InpRowCnt=0
OpRowCnt=0
OpRowCnt2=0

JobName1=Field(Arg1,';',1)
StgLnkName1=Field(Arg1,';',2)

Call DSLogInfo('Checking Records in Job': JobName1,RtnStgChkLnkRec)

RegStageCnt=Arg2

JobHandle1=DSAttachJob(JobName1,DSJ.ERRFATAL)

InpStgName=Field(StgLnkName1,',',1)
InpStgLnkName=Field(StgLnkName1,',',2)
InpRowCnt=DSGetLinkInfo(JobHandle1,InpStgName,InpStgLnkName,DSJ.LINKROWCOUNT)
Call DSLogInfo('Number of Input Records is ': InpRowCnt, RtnStgRecCnt)

OpStgName=Field(StgLnkName1,',',3)
OpLnkName=Field(StgLnkName1,',',4)

If OpStgName<>''
Then
OpRowCnt=DSGetLinkInfo(JobHandle1,OpStgName,OpLnkName,DSJ.LINKROWCOUNT)
Call DSLogInfo('Number of Records in output link is ' : OpRowCnt,RtnStgRecCnt)
End

OpStgName2=Field(StgLnkName1,',',5)
OpLnkName2=Field(StgLnkName1,',',6)

If OpStgName2<>''
Then
OpRowCnt2=DSGetLinkInfo(JobHandle1,OpStgName2,OpLnkName2,DSJ.LINKROWCOUNT)
Call DSLogInfo('Number of Records in output2 link is ' : OpRowCnt2,RtnStgRecCnt)
End


RjctStgName=Field(StgLnkName1,',',7)
RjctLnkName=Field(StgLnkName1,',',8)

If RjctStgName<>''
Then
RjctRowCount=DSGetLinkInfo(JobHandle1,RjctStgName,RjctLnkName,DSJ.LINKROWCOUNT)
Call DSLogInfo('Number of records in reject link is ' : RjctRowCount, RtnStgRecCnt)
End

If INT(InpRowCnt)=INT(OpRowCnt)+INT(RjctRowCount)+INT(OpRowCnt2)
Then
  Call DSLogInfo('Input Row count is exactly as sum of Op and Reject row counts', RtnStgRecCnt)
  FLAG=FLAG
End
Else
Call DSLogInfo('There is data loss in between job',RtnStgRecCnt)
Call DSLogInfo('Aborting Routines',RtnStgRecCnt)
FLAG=FLAG+1
GOTO ExitingRtn
End

ExitingRtn:
Ans=FLAG

{Return value is 0 for No Data Lose in job and in case of data lose return value should be non-zero(1)}