Sub Routine

[harish_s_ampeo]

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

[Mike]

Code: Select all

Call DSExecute('UV',Cmd1,Result1,Status)

You meant UNIX instead of UV here didn't you?

Mike

[harish_s_ampeo]

I tried with both the options -- UNIX and UV...

Both are not working.

[chulett]

Biggest problem? Trying to use sqlplus to connect to Teradata. That would be for Oracle, sorry no clue what the Teradata equivalent would be but it ain't that.

[chulett]

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.