Data Stage Enterprise Edition Server Routines

Routine to Read no of records in a file: ---------------------------------------- Parameters to be passed are Arg1(path),Arg2(file name) Code: ---- vParamFile = Arg1 : "/" : Arg2 vCountVal = 0 OpenSeq vParamFile To FileVar Else Call DSLogWarn("Cannot open ":vParamFile , "Cannot Open ParamFile") End Loop ReadSeq Dummy From FileVar Else Exit ;* at end-of-file vCountVal = vCountVal + 1 Repeat CloseSeq FileVar Ans=vCountVal Return (vCountVal) To send mail: ----------------- Four parameters are to be passed for this routine: Message,Subject,Sendto(Mail id),From (lan id) command = "echo ":Message:" | mail -s ":Subject:" ":SendTo:",":From Call DSExecute("UNIX",command, output, returncode) Ans = returncode To rename the files with timestamp and move files from one directory to another: ----------------------------------------------------------------- $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'mv /path1/path2/filename.txt /newpath/newpath1/filename_`date +"%Y%m%d%H%M%S"`.txt ', Output, SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error", "JobControl") Abort End Else ErrorCode = 0 To connect to db2 database from routine: --------------------------------------- $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'. /export/home/db2inst8/sqllib/db2profile', Output, SystemReturnCode) Call DSExecute("UNIX",'db2 "connect to db2 DSNNAME user USERNAME using PASSWORD"', Output, SystemReturnCode) If SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error", "JobControl") Abort End Else ErrorCode = 0 To get record count from a table: --------------------------------------- PgmName = "CountfromTable" * Set default to empty string Ans = "" T_NAME = Oconv(TableName,"ABC") If Len(Trim(T_NAME)) = 0 Then Message = "No Table name supplied... Abort" Call DSLogFatal(Message,PgmName) ErrorCode = @TRUE Goto TheEnd End Continue: * Format SQL to select count(*) from table Ans = "SELECT COUNT(*) FROM schemaname.":Trim(T_NAME) Return = Ans To find a file in a path1 and moving the file to path2 : ------------------------------------------------------------ $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'find /path/path1/':Arg1, Output, SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogInfo("No Files found for Rename","JobControl") End Else Call DSExecute("UNIX",'mv /path/path1/':Arg1:' /path/path2/':Arg1, Output,SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error","Output is " : Output, "JobControl") Abort End End ErrorCode = 0 Ans = 0