Property	Value
Object Type	Server
Short Description	kduke 2004-06-03 created
Full Description	Job: DSJobReportDbDriver ================================================================= Description: This job will get row counts for all jobs in a sequence. ================================================================= Parameters: 1. TargetDSN 2. TargetPwd 3. TargetUser 4. SeqName - Job sequence 5. EmailFlag 6. CheckParentFlag 7. EmailTo 8. DebugSw ================================================================= Sources: none ================================================================= Targets: none ================================================================= To use in next job: DSJobReportDbDriver ================================================================= Modification History ================================================================= Developer Date Modification description ======== ========= ============================================== kduke 2004-06-03 created ======== ========= ==============================================
Category	EtlStats
Multiple Instance	True
Job Version Number	40.0.0
Hash File Cache Sharing	False
Job Control	* ------------------------------------------------------------ * DSJobReportDbDriver * Decription: * This job will get row counts for all jobs in a sequence. * Also check the parent sequence to see if if failed to run * all the jobs in the sequence. * Should email a message to someone if any other sequence failed. * ------------------------------------------------------------ * Parameters: * 1. TargetDSN * 2. TargetPwd * 3. TargetUser * 4. SeqName * 5. EmailFlag * 6. CheckParentFlag * 7. EmailTo * 8. DebugSw * 9. FilePath * 10. LogoPath * ------------------------------------------------------------ * Created : 2004-07-05 Kim Greggory Duke * Last Modified : 2005-01-05 by Kim. Added check parent logic. * ------------------------------------------------------------ * $INCLUDE DSINCLUDE JOBCONTROL.H * ------------------------------------------------------------ DEFFUN EmailToRtn(JobCategory) CALLING "DSU.EmailToRtn" DEFFUN GetJobCategory(JobName) CALLING "DSU.GetJobCategory" * ------------------------------------------------------------ Ans = "" open 'DS_JOBS' to DsJobs else ErrMsg = "Error: Unable to open DS_JOBS" gosub ErrRtn goto TheEnd end open 'DS_JOBOBJECTS' to DsJobObjects else ErrMsg = "Error: Unable to open DS_JOBOBJECTS" gosub ErrRtn goto TheEnd end If System(91) = 0 Then Shell = "UNIX" Sep = '/' OtherSep = '\' End Else Shell = "DOS" Sep = '\' OtherSep = '/' End * convert OtherSep to Sep in LogoPath * convert OtherSep to Sep in FilePath * ------------------------------------------------------------ * Get JobNo * ------------------------------------------------------------ if downcase(SeqName) <> 'all' then read JobRec from DsJobs, SeqName then JobNo = JobRec<5> ObjId = 'J\':JobNo:'\ROOT' read ObjRec from DsJobObjects, ObjId then MutipleInstanceFlag = ObjRec<59> JobType = ObjRec<40> if JobType = '2' then Cnt = 0 DependJob = SeqName gosub GetRows * ------------------------------------------------------------ * loop thru dependent job names * ------------------------------------------------------------ NoJobs = dcount(ObjRec<31>, @VM) for i = 1 to NoJobs DependJob = ObjRec<31,i> DependType = ObjRec<32,i> if DependType = '0' then * ------------------------------------------------------------ * attach, get status, get row counts * ------------------------------------------------------------ if DependJob = 'GetMaxTableKey' then SubjectName = left(SeqName, len(SeqName)-3) SubjectName = right(SubjectName, len(SubjectName)-3) DependJob = DependJob : '.GetMax':SubjectName:'Key' end gosub GetRows end next i * ------------------------------------------------------------ * Run EtlChkSeqJobsNotRun here * ------------------------------------------------------------ * Setup EtlChkSeqJobsNotRun.SeqName, run it, wait for it to finish, and test for success hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":SeqName, DSJ.ERRFATAL) If NOT(hJob2) Then Call DSLogInfo("Job Attach Failed: EtlChkSeqJobsNotRun.":SeqName, "JobControl") * Abort End Else LastRunStatus = DSGetJobInfo(hJob2, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo("EtlChkSeqJobsNotRun.":SeqName, "Reseting job and last run status") ErrCode = DSRunJob(hJob2, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob2) ErrCode = DSDetachJob(hJob2) hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":SeqName, DSJ.ERRFATAL) If NOT(hJob2) Then Call DSLogFatal("Job Attach Failed: ":"EtlChkSeqJobsNotRun.":SeqName, "JobControl") Abort End end ErrCode = DSSetParam(hJob2, "SeqName", SeqName) ErrCode = DSSetParam(hJob2, "EmailFlag", EmailFlag) ErrCode = DSSetParam(hJob2, "CheckParentFlag", CheckParentFlag) ErrCode = DSSetParam(hJob2, "EmailTo", EmailTo) ErrCode = DSSetParam(hJob2, "DebugSw", DebugSw) ErrCode = DSRunJob(hJob2, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob2) Status = DSGetJobInfo(hJob2, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return Call DSLogInfo("Job Failed: EtlChkSeqJobsNotRun.":SeqName, "JobControl") End End * ------------------------------------------------------------ * Run GenHtmlFromSqlDriver here * get row counts by job name * Setup GenHtmlFromSqlDriver.SeqName, run it, wait for it to finish, and test for success * ------------------------------------------------------------ JobCategory = GetJobCategory(SeqName) hJob3 = DSAttachJob("GenHtmlFromSqlDriver.":SeqName, DSJ.ERRFATAL) If NOT(hJob3) Then Call DSLogInfo("Job Attach Failed: GenHtmlFromSqlDriver.":SeqName, "JobControl") * Abort End Else LastRunStatus = DSGetJobInfo(hJob3, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo("GenHtmlFromSqlDriver.":SeqName, "Reseting job and last run status") ErrCode = DSRunJob(hJob3, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob3) ErrCode = DSDetachJob(hJob3) hJob3 = DSAttachJob("GenHtmlFromSqlDriver.":SeqName, DSJ.ERRFATAL) If NOT(hJob3) Then Call DSLogFatal("Job Attach Failed: ":"GenHtmlFromSqlDriver.":SeqName, "JobControl") Abort End end ErrCode = DSSetParam(hJob3, "FilePath", FilePath) ErrCode = DSSetParam(hJob3, "LogoPath", LogoPath) ErrCode = DSSetParam(hJob3, "SqlScriptsId", "EtlRowByName.sql") ErrCode = DSSetParam(hJob3, "SqlDsn", TargetDSN) ErrCode = DSSetParam(hJob3, "SqlUser", TargetUser) ErrCode = DSSetParam(hJob3, "SqlPwd", TargetPwd) ErrCode = DSSetParam(hJob3, "DebugSw", DebugSw) ErrCode = DSSetParam(hJob3, "EmailFlag", "0") ErrCode = DSSetParam(hJob3, "EmailTo", EmailTo) ErrCode = DSSetParam(hJob3, "MsgJobName", SeqName) ErrCode = DSSetParam(hJob3, "Replacements", JobCategory) ErrCode = DSSetParam(hJob3, "RepSeparator", "~") ErrCode = DSSetParam(hJob3, "FileName", SeqName:"_ByName") ErrCode = DSRunJob(hJob3, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob3) Status = DSGetJobInfo(hJob3, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return Call DSLogInfo("Job Failed: GenHtmlFromSqlDriver.":SeqName, "JobControl") End End * ------------------------------------------------------------ * Run GenHtmlFromSqlDriver here * get row counts by start date * Setup GenHtmlFromSqlDriver.SeqName, run it, wait for it to finish, and test for success * ------------------------------------------------------------ hJob4 = DSAttachJob("GenHtmlFromSqlDriver.":SeqName, DSJ.ERRFATAL) If NOT(hJob4) Then Call DSLogInfo("Job Attach Failed: GenHtmlFromSqlDriver.":SeqName, "JobControl") * Abort End Else LastRunStatus = DSGetJobInfo(hJob4, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo("GenHtmlFromSqlDriver.":SeqName, "Reseting job and last run status") ErrCode = DSRunJob(hJob4, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob4) ErrCode = DSDetachJob(hJob4) hJob4 = DSAttachJob("GenHtmlFromSqlDriver.":SeqName, DSJ.ERRFATAL) If NOT(hJob4) Then Call DSLogFatal("Job Attach Failed: ":"GenHtmlFromSqlDriver.":SeqName, "JobControl") Abort End end ErrCode = DSSetParam(hJob4, "FilePath", FilePath) ErrCode = DSSetParam(hJob4, "LogoPath", LogoPath) ErrCode = DSSetParam(hJob4, "SqlScriptsId", "EtlRowByDate.sql") ErrCode = DSSetParam(hJob4, "SqlDsn", TargetDSN) ErrCode = DSSetParam(hJob4, "SqlUser", TargetUser) ErrCode = DSSetParam(hJob4, "SqlPwd", TargetPwd) ErrCode = DSSetParam(hJob4, "DebugSw", DebugSw) ErrCode = DSSetParam(hJob4, "EmailFlag", "0") ErrCode = DSSetParam(hJob4, "EmailTo", EmailTo) ErrCode = DSSetParam(hJob4, "MsgJobName", SeqName) ErrCode = DSSetParam(hJob4, "Replacements", JobCategory) ErrCode = DSSetParam(hJob4, "RepSeparator", "~") ErrCode = DSSetParam(hJob4, "FileName", SeqName:"_ByDate") ErrCode = DSRunJob(hJob4, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob4) Status = DSGetJobInfo(hJob4, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return Call DSLogInfo("Job Failed: GenHtmlFromSqlDriver.":SeqName, "JobControl") End if EmailFlag then xEmailTo = EmailTo if EmailTo = '' or index('All,Kim,Ron,Sharon',EmailTo,1)=0 then xEmailTo = EmailToRtn(JobCategory) end Cmd = 'SH Scripts':Sep:'Email.ksh ':xEmailTo:' ':SeqName HtmlPath = "Sql2Html" HtmlId = SeqName:"_ByName.html" Cmd := ' ':HtmlPath:Sep:HtmlId HtmlId = SeqName:"_ByDate.html" Cmd := ' ':HtmlPath:Sep:HtmlId Cmd := ' ':HtmlPath:Sep:LogoPath:' ':SeqName:' Row Counts' Call DSLogInfo("Execute: " : Cmd, JobName) execute Cmd capturing output returning RtnCode Call DSLogInfo("Command Results: " : output, JobName) end End end else * ------------------------------------------------------------ * do one job here * ------------------------------------------------------------ DependJob = SeqName gosub GetRows end end else ErrMsg = 'Error: ':SeqName:' not a valid Job. No ':ObjId:' record.' gosub ErrRtn end end else ErrMsg = 'Error: ':SeqName:' not a valid Job.' gosub ErrRtn end end else * ------------------------------------------------------------ * do all jobs here * ------------------------------------------------------------ select DsJobs to 9 loop while readnext DependJob from 9 if index(DependJob, Sep, 1)=0 and index(DependJob, '\', 1)=0 then gosub GetRows end repeat end goto TheEnd * ------------------------------------------------------------ GetRows: Cnt += 1 JobReportName = "DSJobReportDb.":convert(".","_",DependJob) * ------------------------------------------------------------ * Kim skipped to reset a bad job status * ------------------------------------------------------------ goto SkipBadStatus * ------------------------------------------------------------ * DELETE FROM RT_STATUS61 WHERE EVAL "@RECORD<1>" = 'INVOCATION'; * this will delete records so aborted runs do not stop * tricks multiple instance jobs into runable status * ------------------------------------------------------------ read JobReportRec from DsJobs, field(JobReportName, '.', 1) then JobReportNo = JobReportRec<5> open 'RT_STATUS':JobReportNo to RtStatus then delete RtStatus, JobReportName else null close RtStatus end end * ------------------------------------------------------------ SkipBadStatus: read DependJobRec from DsJobs, DependJob then DependJobJobNo = DependJobRec<5> DependJobObjId = 'J\':DependJobJobNo:'\ROOT' read DependJobObjRec from DsJobObjects, DependJobObjId then MutipleInstanceFlag = DependJobObjRec<59> if MutipleInstanceFlag <> 1 or field(DependJob, '.', 1)='GetMaxTableKey' then Call DSLogInfo("Getting row counts for ":DependJob, "JobControl") * ------------------------------------------------------------ * Setup DSJobReportDb, run it, wait for it to finish, and test for success * ------------------------------------------------------------ hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL) If NOT(hJob1) Then ErrMsg = "Error: Job Attach Failed: ":JobReportName gosub ErrRtn goto SkipRun End * ------------------------------------------------------------ * Kim added to reset a bad job status * ------------------------------------------------------------ LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo(JobReportName, "Reseting job and last run status") ErrCode = DSRunJob(hJob1, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob1) ErrCode = DSDetachJob(hJob1) hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL) If NOT(hJob1) Then Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl") Abort End end * ------------------------------------------------------------ * ErrCode = DSSetParam(hJob1, "dsjobcmd", "dsjob -report") ErrCode = DSSetParam(hJob1, "projName", @WHO) ErrCode = DSSetParam(hJob1, "jobName", DependJob) ErrCode = DSSetParam(hJob1, "TargetDSN", TargetDSN) ErrCode = DSSetParam(hJob1, "TargetUser", TargetUser) ErrCode = DSSetParam(hJob1, "TargetPwd", TargetPwd) ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob1) Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return ErrMsg = "Error: Job Failed: ":JobReportName gosub ErrRtn End end else ErrMsg = 'Error: ':DependJob:' unable to get rows counts for multiple instance job without InvocationId.' gosub ErrRtn end end else ErrMsg = 'Error: ':DependJob:' not a valid Job. No ':DependJobObjId:' record.' gosub ErrRtn end end else ErrMsg = 'Error: ':DependJob:' not a valid Job.' gosub ErrRtn end SkipRun: return * ------------------------------------------------------------ ErrRtn: Call DSLogInfo(ErrMsg , "JobControl") * Call DSLogFatal(ErrMsg, "JobControl") * abort return * ------------------------------------------------------------ TheEnd:

Parameter Name	Prompt	Default Value	Help Text
TargetDSN	TargetDSN	kduke
TargetUser	TargetUser	etluser
TargetPwd	TargetPwd	LDH@9:VA=93L0O5I<;J
SeqName	SeqName	SeqTimeDim
EmailFlag	EmailFlag	1
CheckParentFlag	CheckParentFlag	1	Not used currently.
EmailTo	EmailTo	xxx	Will calculate.
DebugSw	DebugSw	0
FilePath	FilePath	./
LogoPath	LogoPath	OurLogo.jpg

- Source Stages (0)  + Source Stages (0)

- Processing Stages (0)  + Processing Stages (0)

- Target Stages (0)  + Target Stages (0)