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
Sub Routine
Moderators: chulett, rschirm, roy
Code: Select all
Call DSExecute('UV',Cmd1,Result1,Status)
Mike
-
- Participant
- Posts: 26
- Joined: Tue Dec 18, 2007 6:31 am
OK, by now you've discovered 'bteq' and jumped into a couple of other people's posts. Let's come back here.
Do you not have access to Teradata people where you work that could help you with the syntax you need to accomplish this? It really has nothing to do with DataStage - first get your 'script' working outside of DataStage, after that running it from inside the tool is the easy part.
Do you not have access to Teradata people where you work that could help you with the syntax you need to accomplish this? It really has nothing to do with DataStage - first get your 'script' working outside of DataStage, after that running it from inside the tool is the easy part.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers