Sub Routine
Posted: Wed Jan 07, 2009 7:44 am
Hi All,
I have a sub routine which retrieves data from a table and inserts into another table along with other job statistics info. When i used this as a after subroutine in a job, it executes successfully, but the data is not getting inserted.
the routine used is :
$INCLUDE DSINCLUDE JOBCONTROL.H
ErrorCode = 0
DwUser= Field(InputArg,';',1)
DdwPass= Field(InputArg,';',2)
DwDb= Field(InputArg,';',3)
JobHandle = DSJ.ME
JobName = DSGetJobInfo(JobHandle,DSJ.JOBNAME)
StartTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBSTARTTIMESTAMP)
EndTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBLASTTIMESTAMP)
RecordSourcecount = DSGetLinkInfo(DSJ.ME,"Transformer_6","input",DSJ.LINKROWCOUNT)
RecordTargetCount1 = DSGetLinkInfo(DSJ.ME,"Transformer_6","out1",DSJ.LINKROWCOUNT)
Equate DBstr To DwUser:"/":"'":DwPass:"'":"@":DwDb
Cmd1 = "echo 'select BI_ETL_BTCH_ID from CTRLDB_DEV.TEBIL_ETL_BTCH_ID_LOOKUP WHERE CUR_IND='Y'; ' | sqlplus -s " : DBstr
Call DSExecute('UV',Cmd1,Result1,Status)
Er = Index (Result1,"ERROR",1)
If Er <> 0 then
Call DSLogWarn("Error occured while accessing the TEBIL_ETL_BTCH_ID_LOOKUP TABLE, Status = 2 [Aborted]","Check")
End ;
Batchid=Result1
Cmd2 = "echo 'insert into CTRLDB_DEV.TJALL_JOB_AUDIT_LOG (BI_ETL_BTCH_ID,JOB_ID,RUN_ID,LOAD_UTIL_NM,STRT_DT,END_DT,READ_REC_COUNT,INS_REC_COUNT)
Values (" : Batchid : ", '1000'," : StartTimeStamp : ", " : JobName : "," : StartTimeStamp : "," : EndTimeStamp : "," : RecordSourcecount : "," : RecordTargetCount1 :");' | sqlplus -s " : DBstr
Call DSExecute('UNIX',Cmd2,Result2,Status)
Er = Index (Result2,"ERROR",1)
If Er <> 0 then
Call DSLogWarn("Error occured while accessing the AUDIT TABLE, Status = 2 [Aborted]","Check")
End ;
Ans = Status
I also tried writing the output of all the statistics values, result1 into a text file , the statistics values are getting loaded correctly but value of result1 is giving error as 'Verb echo not in VOC'.
the database used is Teradata, so also i have a doubt about the way i am connecting in the routine.
Please help me on this issue.
Thanks & Regards,
Harish
I have a sub routine which retrieves data from a table and inserts into another table along with other job statistics info. When i used this as a after subroutine in a job, it executes successfully, but the data is not getting inserted.
the routine used is :
$INCLUDE DSINCLUDE JOBCONTROL.H
ErrorCode = 0
DwUser= Field(InputArg,';',1)
DdwPass= Field(InputArg,';',2)
DwDb= Field(InputArg,';',3)
JobHandle = DSJ.ME
JobName = DSGetJobInfo(JobHandle,DSJ.JOBNAME)
StartTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBSTARTTIMESTAMP)
EndTimeStamp = DSGetJobInfo (JobHandle, DSJ.JOBLASTTIMESTAMP)
RecordSourcecount = DSGetLinkInfo(DSJ.ME,"Transformer_6","input",DSJ.LINKROWCOUNT)
RecordTargetCount1 = DSGetLinkInfo(DSJ.ME,"Transformer_6","out1",DSJ.LINKROWCOUNT)
Equate DBstr To DwUser:"/":"'":DwPass:"'":"@":DwDb
Cmd1 = "echo 'select BI_ETL_BTCH_ID from CTRLDB_DEV.TEBIL_ETL_BTCH_ID_LOOKUP WHERE CUR_IND='Y'; ' | sqlplus -s " : DBstr
Call DSExecute('UV',Cmd1,Result1,Status)
Er = Index (Result1,"ERROR",1)
If Er <> 0 then
Call DSLogWarn("Error occured while accessing the TEBIL_ETL_BTCH_ID_LOOKUP TABLE, Status = 2 [Aborted]","Check")
End ;
Batchid=Result1
Cmd2 = "echo 'insert into CTRLDB_DEV.TJALL_JOB_AUDIT_LOG (BI_ETL_BTCH_ID,JOB_ID,RUN_ID,LOAD_UTIL_NM,STRT_DT,END_DT,READ_REC_COUNT,INS_REC_COUNT)
Values (" : Batchid : ", '1000'," : StartTimeStamp : ", " : JobName : "," : StartTimeStamp : "," : EndTimeStamp : "," : RecordSourcecount : "," : RecordTargetCount1 :");' | sqlplus -s " : DBstr
Call DSExecute('UNIX',Cmd2,Result2,Status)
Er = Index (Result2,"ERROR",1)
If Er <> 0 then
Call DSLogWarn("Error occured while accessing the AUDIT TABLE, Status = 2 [Aborted]","Check")
End ;
Ans = Status
I also tried writing the output of all the statistics values, result1 into a text file , the statistics values are getting loaded correctly but value of result1 is giving error as 'Verb echo not in VOC'.
the database used is Teradata, so also i have a doubt about the way i am connecting in the routine.
Please help me on this issue.
Thanks & Regards,
Harish