New EtlStats.zip posted

kduke · Post by **kduke** » Sun Feb 06, 2005 1:29 am

I posted a new EtlStats.zip. I added logic to email row counts and log files on failed jobs.

I have a new process to report completeness on any dimension.

The documentation is http://www.duke-consulting.com/KimD/Etl ... Index.html All of this is available on my tips page.

Sample row counts http://www.duke-consulting.com/KimD/Etl ... wHist.html

Sample log reports http://www.duke-consulting.com/KimD/Etl ... m_Log.html

Sample completeness reports http://www.duke-consulting.com/KimD/Etl ... 50105.html

I will also post this on ADN.

ray.wurlod · Post by **ray.wurlod** » Sun Feb 06, 2005 1:46 am

As we say in this part of the world - ONYA!

(It's short for GOOD ONYA, but the first syllable is really redundant on a number of levels.)

Really need a praise emoticon. :praise:

kduke · Post by **kduke** » Sun Feb 06, 2005 9:00 am

There are a lot of jobs here.

GenHtmlFromSql is a job which will turn a SQL statement into a html page and email it to you.

GenHtmlFromSqlDriver is a driver job for the above job. It will give you a list of SQL statements already built and used by me. These SQL files are stored in a driectory called SqlScripts below the project. It can replace :1 and :2 with values supplied in the parameter Replacements and RepSeparator. So to get a log report then the job name would be loaded in Replacements. You would pick the SqlScriptsId called RtLogRpt.sql. The SQL is below.

Code: Select all

select
   TIMESTAMP,
   SEVERITY,
   FULL.TEXT
from
   RT_LOG:1
where
   EVAL "@ID" NOT LIKE '//%'
   AND @ID >= :2
order by 
   TIMESTAMP
;

I have been working on most of this for months. I usually only get a few hours a week to work on these jobs. Most of these jobs were written in my spare time.

The email script is a UNIX only script. If someone writes a DOS version then please let me know. The email message will attach your logo jpg file and all the html files as attachments. If you create a folder for each run then save these off to a folder like 20050207. This way you can compare this run to the previous. Your row counts should not vary that much from run to run. At least that is the theory.

Most of the source code is included. If you make any improvements then please post them to this thread or email them to me. This was a lot of work and I know it is very complex but I think it is very useful information. We have a small group of DataStage developers so the more tools we have to help monitor our processes then the better off we are.

All of this is in Ds7.1. I included text files with either .job or .rtn extensions so you might be able to convert them to older versions of DataStage. Once again please share anything you think improves it in your mind.

kduke · Post by **kduke** » Sun Feb 06, 2005 9:03 am

I forgot the SQL statement above is controlled by a job named GenHtmlJobLog. This job figures out the job number from the job name to make the proper replacements above. So you do some complicated stuff with these jobs.

kduke · Post by **kduke** » Wed Feb 09, 2005 10:03 pm

I added a job to monitor our main sequence and all children sequences. It will email the logs of the failed jobs. I plan on making it page the developer soon. I think this is cool. It requires EtlStats.zip above.

Code: Select all

* ------------------------------------------------------------
* MonitorSeq
* ------------------------------------------------------------
* Decription:
* This job will wait on the main sequence to start running.
* It will build a list of the sequences to run.
* As they run it will check on them after each sleep interval.
* When the sequences finish then run EtlChkSeqJobsNotRun
* This will make sure all jobs in this child sequence ran properly.
* ------------------------------------------------------------
* Parameters:
*  1. SeqName
*  2. EmailFlag
*  3. EmailTo
*  4. DebugSw
* ------------------------------------------------------------
* Created       : 2005-01-05 Kim Greggory Duke
* Last Modified : 2005-01-05 Kim Created.
* ------------------------------------------------------------
      Ans = ""
      JobName = 'MonitorSeq'
      ThisJobName = JobName
      StopId = JobName:'Stop.':SeqName
      CrLf = char(13):char(10)
      SleepTime = 60 * 3                 ; * seconds
      * SleepTime = 30                     ; * seconds
      MaxWaitTime = 60 * 30              ; * seconds
      ExceededWaitTime = @FALSE
      MaxRunTime = 60 * 60 * 24          ; * seconds
      ExceededRunTime = @FALSE
* ------------------------------------------------------------
      open 'VOC' to VOC else
         ErrMsg = "Error: Unable to open VOC"
         gosub ErrRtn
         goto TheEnd
      end
      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
* ------------------------------------------------------------
      MyStartDate = DATE()
      MyStartTime = TIME()
      MyStartedDateTime = oconv(MyStartDate, "D4-YMD[4,2,2]"):" ":oconv(MyStartTime, "MTS")
      MainSeqName = SeqName
      MainStartDate = ''
      MainStartTime = ''
      MainSeqStatus = ''
      DependJobList = ''
* ------------------------------------------------------------
* Get JobNo
* ------------------------------------------------------------
      read JobRec from DsJobs, SeqName then
         JobNo = JobRec<5>
         JobCategory = JobRec<3>
         ObjId = 'J\':JobNo:'\ROOT'
         read ObjRec from DsJobObjects, ObjId then
            MutipleInstanceFlag = ObjRec<59>
            JobType = ObjRec<40>
            if JobType = '2' then
               Cnt = 0
* ------------------------------------------------------------
* build job list
* ------------------------------------------------------------
               NoJobs = dcount(ObjRec<31>, @VM)
               for i = 1 to NoJobs
                  DependJob = ObjRec<31,i>
                  DependType = ObjRec<32,i>
                  if DependType = '0' then
                     DependJobList<-1> = DependJob
                  end
               next i
            end else
* ------------------------------------------------------------
* not a sequence
* ------------------------------------------------------------
               ErrMsg = 'Error: ':SeqName:' not a valid sequence'
               gosub ErrRtn
               goto TheEnd
            end
         end else
* ------------------------------------------------------------
* SeqName not a valid job
* ------------------------------------------------------------
            ErrMsg = 'Error: ':SeqName:' not a valid Job. No ':ObjId:' record.'
            gosub ErrRtn
            goto TheEnd
         end
      end else
         ErrMsg = 'Error: ':SeqName:' not a valid Job.'
         gosub ErrRtn
         goto TheEnd
      end
* ------------------------------------------------------------
* Main
* ------------------------------------------------------------
* Delete VOC, MonitorSeqStop
* Get SeqName.
* Loop
*   Check VOC, MonitorSeqStop
*     stop
*   Attach to job
*   get status
* until running
* Loop
*   Get DependJob from SeqJob
*   if finished running
*     run EtlChkSeqJobsNotRun
*     delete from list
*   JobName = next in list
* while jobs left to check
* done monitoring
* ------------------------------------------------------------
      delete VOC, StopId else null
* ------------------------------------------------------------
      DependJob = SeqName
      ChkSeqName = SeqName
      gosub GetStartTime
      if DebugSw then
         LogMsg = " MyStartedDateTime: ":MyStartedDateTime:" StartedDateTime: ":StartedDateTime
         Call DSLogInfo(LogMsg, "JobControl")
      end
      loop
         gosub GetStartTime
         Begin case
            case LastRunStatus = DSJS.RUNNING
               SeqStarted = @TRUE
            case MyStartedDateTime <= StartedDateTime
               SeqStarted = @TRUE
            case @TRUE
               SeqStarted = @FALSE
         End case
         gosub GetMyRunTime
         read VocRec from VOC, StopId then
            ErrMsg = 'Error: Stopped by VOC, ':StopId
            gosub ErrRtn
            goto TheEnd
         end
         if ExceededWaitTime then
            ErrMsg = 'Error: ExceededWaitTime ':ExceededWaitTime:' seconds exceeded. ':SeqName:' job never started.'
            gosub ErrRtn
            goto TheEnd
         end
         sleep SleepTime
      until SeqStarted do
      repeat
* ------------------------------------------------------------
* loop thru dependent job names
* ------------------------------------------------------------
      ContFlag = @TRUE
      Loop
         read VocRec from VOC, StopId then
            ContFlag = @FALSE
         end
         if DependJobList = '' then
            ContFlag = @FALSE
         end
      while ContFlag do
         JobDelete = @FALSE
         NoJobs = dcount(DependJobList, @FM)
         for i = 1 to NoJobs until JobDelete
            DependJob = DependJobList<i>
            if DebugSw then
               LogMsg = " Checking run times: ":DependJob
               Call DSLogInfo(LogMsg, "JobControl")
            end
            gosub GetStartTime
            if JobStarted and JobFinished then
               if IsSequence then
* ------------------------------------------------------------
* Run EtlChkSeqJobsNotRun here
* ------------------------------------------------------------
* Setup EtlChkSeqJobsNotRun.DependJob, run it, wait for it to finish, and test for success
                  hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
                  If NOT(hJob2) Then
                     Call DSLogInfo("Job Attach Failed: EtlChkSeqJobsNotRun.":DependJob, "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.":DependJob, "Reseting job and last run status")
                        ErrCode = DSRunJob(hJob2, DSJ.RUNRESET)
                        ErrCode = DSWaitForJob(hJob2)
                        ErrCode = DSDetachJob(hJob2)
                        hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
                        If NOT(hJob2) Then
                           Call DSLogFatal("Job Attach Failed: ":"EtlChkSeqJobsNotRun.":DependJob, "JobControl")
                           Abort
                        End
                     end
                     ErrCode = DSSetParam(hJob2, "SeqName", DependJob)
                     ErrCode = DSSetParam(hJob2, "EmailFlag", EmailFlag)
                     ErrCode = DSSetParam(hJob2, "CheckParentFlag", '1')
                     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.":DependJob, "JobControl")
                     End
                  End
               End                       ; * IsSequence
               if DebugSw then
                  LogMsg = " DependJob: ":DependJob:" finished."
                  Call DSLogInfo(LogMsg, "JobControl")
               end
               JobDelete = @TRUE
               del DependJobList<i>
            end else
               if ErrMsg <> '' or (JobStarted and JobAborted) then
                  JobDelete = @TRUE
                  del DependJobList<i>
               end
            end
         next i
         gosub GetMyRunTime
         if ExceededRunTime then
            ErrMsg = 'Error: ExceededRunTime ':ExceededRunTime:' seconds exceeded. ':SeqName:' job never finished.'
            gosub ErrRtn
            goto TheEnd
         end
         sleep SleepTime
      repeat
* ------------------------------------------------------------
      DependJob = SeqName
      hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
      If NOT(hJob2) Then
         Call DSLogInfo("Job Attach Failed: EtlChkSeqJobsNotRun.":DependJob, "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.":DependJob, "Reseting job and last run status")
            ErrCode = DSRunJob(hJob2, DSJ.RUNRESET)
            ErrCode = DSWaitForJob(hJob2)
            ErrCode = DSDetachJob(hJob2)
            hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
            If NOT(hJob2) Then
               Call DSLogFatal("Job Attach Failed: ":"EtlChkSeqJobsNotRun.":DependJob, "JobControl")
               Abort
            End
         end
         ErrCode = DSSetParam(hJob2, "SeqName", DependJob)
         ErrCode = DSSetParam(hJob2, "EmailFlag", EmailFlag)
         ErrCode = DSSetParam(hJob2, "CheckParentFlag", '1')
         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.":DependJob, "JobControl")
         End
      End
      goto TheEnd
* ------------------------------------------------------------
GetMyRunTime:
      MyRunTime = (DATE() - MyStartDate) * 24 * 60 * 60
      MyRunTime += TIME() - MyStartTime
      if MyRunTime > MaxWaitTime then
         ExceededWaitTime = @TRUE
      end
      if MyRunTime > MaxRunTime then
         ExceededRunTime = @TRUE
      end
      return
* ------------------------------------------------------------
GetStartTime:
      ErrMsg = ''
      Cnt += 1
      read DependJobRec from DsJobs, DependJob then
         DependJobJobNo = DependJobRec<5>
         DependJobObjId = 'J\':DependJobJobNo:'\ROOT'
         read DependJobObjRec from DsJobObjects, DependJobObjId then
            JobType = DependJobObjRec<40>
            if JobType = '2' then
               IsSequence = @TRUE
            end else
               IsSequence = @FALSE
            end
            MutipleInstanceFlag = DependJobObjRec<59>
            if MutipleInstanceFlag <> 1 then

               * Call DSLogInfo("Getting row counts for ":DependJob, "JobControl")
* ------------------------------------------------------------
* Setup DSJobReportDb, run it, wait for it to finish, and test for success
* ------------------------------------------------------------
               hJob1 = DSAttachJob(DependJob, DSJ.ERRFATAL)
               If NOT(hJob1) Then
                  ErrMsg = "Error: Job Attach Failed: ":DependJob
                  gosub ErrRtn
                  goto SkipCheck
               End
* ------------------------------------------------------------
               JobAborted = @FALSE
               JobFinished = @FALSE
               LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
               Begin case
                  case LastRunStatus = DSJS.RUNNING
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Running in ":ChkSeqName
                  case LastRunStatus = DSJS.RUNOK
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Finished in ":ChkSeqName
                     JobFinished = @TRUE
                  case LastRunStatus = DSJS.RUNWARN
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Finished (see log) in ":ChkSeqName
                     JobsWithWarnings = DependJob
                     JobFinished = @TRUE
                  case LastRunStatus = DSJS.RUNFAILED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Aborted in ":ChkSeqName
                     JobAborted = @TRUE
                  case LastRunStatus = DSJS.CRASHED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Aborted in ":ChkSeqName
                     JobAborted = @TRUE
                  case LastRunStatus = DSJS.STOPPED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Stopped in ":ChkSeqName
                     JobAborted = @TRUE
                  case @TRUE
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Unknown in ":ChkSeqName
                     JobAborted = @TRUE
               end case
               if DebugSw then Call DSLogInfo(LogMsg, "JobControl")
               StartedDateTime = DSGetJobInfo(hJob1, DSJ.JOBSTARTTIMESTAMP)
               if MainSeqName = DependJob then
                  MainStartedDateTime = StartedDateTime
                  MainSeqStatus = LastRunStatus
               end
               ErrCode = DSDetachJob(hJob1)
               LogMsg = LogMsg:" Started: ":StartedDateTime
               LogMsgs<-1> = Cnt:'. ':LogMsg
               if MainStartedDateTime <= StartedDateTime then
                  JobStarted = @TRUE
               end else
                  JobStarted = @FALSE
               end
            end else
               ErrMsg = 'Error: ':DependJob:' unable to get status 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
SkipCheck:
      return
* ------------------------------------------------------------
ErrRtn:
      Call DSLogInfo(ErrMsg , "JobControl")
      * Call DSLogFatal(ErrMsg, "JobControl")
      * abort
      return
* ------------------------------------------------------------
TheEnd:

kduke · Post by **kduke** » Fri Feb 11, 2005 12:16 pm

I found a bug in the output from dsjob -report XML. If a job passes midnight then it does not calculate ElapsedSecs or ElapsedTime both are zero.

I fixed this by calculating it from StartDateTime and EndDateTime.

I will post my fix this weekend or next week because I am traveling this weekend. I also have a job to fix the tables effected. I will post that as well.

roy · Post by **roy** » Tue Feb 15, 2005 6:25 am

I take my hat off Kim

Rarely I find myself speachless (I guess this is one of those times)

sudharsanan · Post by **sudharsanan** » Tue Feb 15, 2005 7:02 am

Thank YOU KIM for posting this...I would be a great help for beginner's like me to start understanding complex datastage comcepts.. Thanks once again KIM...

kduke · Post by **kduke** » Tue Feb 15, 2005 7:42 am

Let me know if anyone gets this to work. It is so complex on some of this stuff I was afraid to release it. The results are lots better than I expected though. The web pages with the log files are cool. I like the row counts too. I think I need to add the date to the attachments so when you save them it saves them separately like DayDim_RowCounts_20050215.html. I need to post the fix above so I will add that as well.

I really appreciate the compliments. You would be surprised how few comments I do get. I know people are using this stuff. I wonder if I have written anything that the great Ray would ever use? He never seems to need our help or learn something from us mere mortals.

kduke · Post by **kduke** » Tue Feb 15, 2005 8:08 pm

New EtlStats.zip is posted along with updated MonitorSeq and a couple more jobs. Please download from my tips page or ADN. Rows per second and Elapsed Time are fixed. I changed the documentation from bmp files to jpg files so it loads lots faster. Sorry about that.

You got to try GenHtmlFromSqlDriver. It is just fun. Try DsJobSequences2.sql. If a column ends in .htm or .html then it creates a link out of it.

kduke · Post by **kduke** » Wed Feb 16, 2005 6:29 pm

I got this email. I thought I would answer it here in case someone else needed the same answer.

while i was looking in your jobs, i have got
a question and a requiremnt as well(like if the job
aborts it won't tell you the reason, it will tell u
onle 'it is aborted' in the email notification stage).
Can we email the log's at the end of the sequence if
the job fails. I have seen one of your job with
similar purpose but..is it possible at the end of the
sequence. Like capturing the job number during job
runtime and using it in a job with UV odbc stage with
select statement select* from rt_lognnn..lot of ideas
.i don't know is it possible or not.

kduke · Post by **kduke** » Wed Feb 16, 2005 6:47 pm

GenHtmlJobLog is the job that will generate a web page with the job log information in it.

Parameters:
1. EmailFlag
2. JobNameToLog
3. EmailTo
4. DebugSw

EmailFlag = 1 if you want it to email you the web page as an attachment otherwise the results will be stored in "Sql2Html" folder below the project. The filename will be JobNameToLog : "_log.html".

JobNameToLog is the job name you want the log file from.

EmailTo is a shortcut to the real email. You need to change this to your email. In my example Kim will send it to kim_g_duke@hotmail.com. "Kim" gets substituted in Email.ksh. This shell script should be in a directory below the project called "Scripts".

DebugSw should normally be 0. If it is 1 then it will show you the SQL used to retrieve the log file information. I used this to help debug issues.

This job run job GenHtmlFromSql.

* Parameters:
* 1. FilePath = ./
* 2. LogoPath = OurLogo.jpg
* 3. FileName = JobNameToLog : "_log.html"
* 4. SqlStmt = "
select
TIMESTAMP,
SEVERITY,
FULL.TEXT
from
RT_LOG61
where
EVAL "@ID" NOT LIKE '//%'
order by
TIMESTAMP
;"
* 5. SqlDsn = localuv
* 6. SqlUser = null
* 7. SqlPwd = null
* 8. DebugSw = 1 (1 prints SQL)
* 9. EmailFlag = 1
* 10. EmailTo = xxx (use EmailToRtn)
* 11. MsgJobName = GenHtmlFromSql
* 12. EmailHeader = Sql Results

EmailToRtn is a routine to figure out which email user to really mail this message to if EmailTo field is invalid. The EmailToRtn will pick a email address based on the job category of MsgJobName. You will also need to modify this routine. SqlDsn will use any valid ODBC DSN setup by you. The default is the localuv which runs SQL against hash files like DS_JOBS. You can leave the user and password blank to go against hash files.

kduke · Post by **kduke** » Wed Feb 16, 2005 6:55 pm

The problem is how do you know which job failed in a sequence. Job EtlChkSeqJobsNotRun will figure out which job in a sequence failed. It will mail you the log files of all jobs that finished with other than "OK" status.

* Parameters:
* 1. SeqName
* 2. EmailFlag
* 3. CheckParentFlag - not used yet
* 4. EmailTo
* 5. DebugSw

SeqName is the name of the job sequence you want to find all the jobs which failed. The other parameters are same as above.

This job will call GenHtmlJobLog for you.

kduke · Post by **kduke** » Wed Feb 16, 2005 7:45 pm

But the best way to implement this is at the end of each sequence either good end or through the exception handler then run DSJobReportDbDriver. This job will call all the jobs above. It will also calcutate your row counts and save them. It will also email you the row counts of the last run of all the jobs in this sequence.

* Parameters:
* 1. TargetDSN
* 2. TargetPwd
* 3. TargetUser
* 4. SeqName
* 5. EmailFlag
* 6. CheckParentFlag
* 7. EmailTo
* 8. DebugSw
* 9. FilePath
* 10. LogoPath

TargetDSN should be an ODBC DSN and an Oracle SID otherwise you need to change it. You will need to add tables ETL_JOB_HIST, ETL_ROW_HIST and ETL_PARAM_HIST to your target data warehouse. All the other parameters are the same as above.

FilePath was so Sql2Html files can be moved. You will also need a directory called "KimD" below your project. The xml files from JobReport are stored here.

LogoPath is used in all the jobs and routines which generate html files. It is so you can display your company logo in the top left hand corner of each web page. If you store this file in Sql2Html directory then it will attach this file to the email messages. I think my company logo is zipped up in this directory now. You need to change it to whatever.

This job was originally in all of our sequences so I did not want to add another job to all of the sequences. So I made this one job run all the others.

The MonitorSeq will also run all these jobs for you as well soon.

kduke · Post by **kduke** » Thu Feb 17, 2005 2:10 pm

Done. This will get row counts post to Oracle. Email you the row counts. Email you the logs of all jobs which failed or had warnings. You do not need to modify any sequence. You kick it off right before your sequence.

MonitorSeq:

Code: Select all

* ------------------------------------------------------------
* MonitorSeq
* ------------------------------------------------------------
* Decription:
* This job will wait on the main sequence to start running.
* It will build a list of the sequences to run.
* As they run it will check on them after each sleep interval.
* When the sequences finish then run EtlChkSeqJobsNotRun
* or get row counts by running DSJobReportDbDriver.
* This will make sure all jobs in this child sequence ran properly.
* ------------------------------------------------------------
* Business rules: 
* Run this first then run the sequence to monitor soon after.
* ------------------------------------------------------------
* Parameters: 
*  1. SeqName
*  2. EmailFlag
*  3. EmailTo
*  4. DebugSw
*  5. GetRowCountsFlag
*  6. TargetDSN
*  7. TargetUser
*  8. TargetPwd
*  9. CheckParentFlag
* 10. FilePath
* 11. LogoPath
* ------------------------------------------------------------
* Created       : 2005-01-05 Kim Greggory Duke
* Last Modified : 2005-01-05 Kim Created.
* ------------------------------------------------------------
      Ans = ""
      JobName = 'MonitorSeq'
      ThisJobName = JobName
      StopId = JobName:'Stop.':SeqName
      SleepTime = 60 * 1                 ; * seconds
      MaxWaitTime = 60 * 30              ; * seconds
      ExceededWaitTime = @FALSE
      MaxRunTime = 60 * 60 * 24          ; * seconds
      ExceededRunTime = @FALSE
      CrLf = char(13):char(10)
* ------------------------------------------------------------
      open 'VOC' to VOC else
         ErrMsg = "Error: Unable to open VOC"
         gosub ErrRtn
         goto TheEnd
      end
      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
* ------------------------------------------------------------
      MyStartDate = DATE()
      MyStartTime = TIME()
      MyStartedDateTime = oconv(MyStartDate, "D4-YMD[4,2,2]"):" ":oconv(MyStartTime, "MTS")
      MainSeqName = SeqName
      MainStartDate = ''
      MainStartTime = ''
      MainSeqStatus = ''
      DependJobList = ''
* ------------------------------------------------------------
* Get JobNo
* ------------------------------------------------------------
      read JobRec from DsJobs, SeqName then
         JobNo = JobRec<5>
         JobCategory = JobRec<3>
         ObjId = 'J\':JobNo:'\ROOT'
         read ObjRec from DsJobObjects, ObjId then
            MutipleInstanceFlag = ObjRec<59>
            JobType = ObjRec<40>
            if JobType = '2' then
               Cnt = 0
* ------------------------------------------------------------
* build job list
* ------------------------------------------------------------
               NoJobs = dcount(ObjRec<31>, @VM)
               for i = 1 to NoJobs
                  DependJob = ObjRec<31,i>
                  DependType = ObjRec<32,i>
                  if DependType = '0' then
                     DependJobList<-1> = DependJob
                  end
               next i
            end else
* ------------------------------------------------------------
* not a sequence
* ------------------------------------------------------------
               ErrMsg = 'Error: ':SeqName:' not a valid sequence'
               gosub ErrRtn
               goto TheEnd
            end
         end else
* ------------------------------------------------------------
* SeqName not a valid job
* ------------------------------------------------------------
            ErrMsg = 'Error: ':SeqName:' not a valid Job. No ':ObjId:' record.'
            gosub ErrRtn
            goto TheEnd
         end
      end else
         ErrMsg = 'Error: ':SeqName:' not a valid Job.'
         gosub ErrRtn
         goto TheEnd
      end
* ------------------------------------------------------------
* Main
* ------------------------------------------------------------
* Delete VOC, MonitorSeqStop
* Get SeqName.
* Loop
*   Check VOC, MonitorSeqStop
*     stop
*   Attach to job
*   get status
* until running
* Loop
*   Get DependJob from SeqJob
*   if finished running
*     run EtlChkSeqJobsNotRun
*     delete from list
*   JobName = next in list
* while jobs left to check
* done monitoring
* ------------------------------------------------------------
      delete VOC, StopId else null
* ------------------------------------------------------------
      DependJob = SeqName
      ChkSeqName = SeqName
      gosub GetStartTime
      LogMsg = "Monitoring: ":SeqName
      Call DSLogInfo(LogMsg, "JobControl")
      if DebugSw then
         LogMsg = "MyStartedDateTime: ":MyStartedDateTime:" StartedDateTime: ":StartedDateTime
         Call DSLogInfo(LogMsg, "JobControl")
      end
      loop
         gosub GetStartTime
         Begin case
            case LastRunStatus = DSJS.RUNNING
               SeqStarted = @TRUE
               if DebugSw then
                  LogMsg = "Sequence: ":SeqName:" running."
                  Call DSLogInfo(LogMsg, "JobControl")
               end
            case MyStartedDateTime <= StartedDateTime
               SeqStarted = @TRUE
               if DebugSw then
                  LogMsg = "Sequence: ":SeqName:" started."
                  Call DSLogInfo(LogMsg, "JobControl")
               end
            case @TRUE
               SeqStarted = @FALSE
         End case
         gosub GetMyRunTime
         if DebugSw then
            LogMsg = "Looking for VOC, ":StopId
            Call DSLogInfo(LogMsg, "JobControl")
         end
         read VocRec from VOC, StopId then
            ErrMsg = 'Error: Stopped by VOC, ':StopId
            gosub ErrRtn
            goto TheEnd
         end
         if ExceededWaitTime then
            ErrMsg = 'Error: ExceededWaitTime ':ExceededWaitTime:' seconds exceeded. ':SeqName:' job never started.'
            gosub ErrRtn
            goto TheEnd
         end
         if not(SeqStarted) then
            LogMsg = "Sleeping: ":SleepTime:" seconds."
            Call DSLogInfo(LogMsg, "JobControl")
            sleep SleepTime
         end
      until SeqStarted repeat
* ------------------------------------------------------------
* loop thru dependent job names
* ------------------------------------------------------------
      if DebugSw then
         LogMsg = "Sequence Started: ":SeqName:" First loop done."
         Call DSLogInfo(LogMsg, "JobControl")
         LogMsg = "MyStartedDateTime: ":MyStartedDateTime:" StartedDateTime: ":StartedDateTime
         Call DSLogInfo(LogMsg, "JobControl")
      end
      LogMsg = "Sequence Started: ":SeqName:" ":StartedDateTime
      Call DSLogInfo(LogMsg, "JobControl")
      LogMsg = "Monitoring children: ":SeqName
      Call DSLogInfo(LogMsg, "JobControl")
      JobsStarted = ''
      ContFlag = @TRUE
      Loop
         if DebugSw then
            LogMsg = "Looking for VOC, ":StopId
            Call DSLogInfo(LogMsg, "JobControl")
         end
         read VocRec from VOC, StopId then
            ContFlag = @FALSE
            ErrMsg = 'Error: Stopped by VOC, ':StopId
            gosub ErrRtn
            goto TheEnd
         end
         if DependJobList = '' then
            ContFlag = @FALSE
         end
      while ContFlag do
         JobDelete = @FALSE
         NoJobs = dcount(DependJobList, @FM)
         for i = 1 to NoJobs until JobDelete
            DependJob = DependJobList<i>
            if DebugSw then
               LogMsg = "Checking run times: ":DependJob
               Call DSLogInfo(LogMsg, "JobControl")
            end
            gosub GetStartTime
            if JobStarted and ErrMsg = '' then
               locate DependJob in JobsStarted setting AttrNo else
                  JobsStarted<-1> = DependJob
                  LogMsg = DependJob:": job started. ":StartedDateTime
                  Call DSLogInfo(LogMsg, "JobControl")
               end
            end
            if JobStarted and JobFinished then
               if IsSequence then
                  gosub RunJobsSub
               End                       ; * IsSequence
               LogMsg = DependJob:": job finished. ":FinishedDateTime:" in ":ElapsedTime:" seconds."
               Call DSLogInfo(LogMsg, "JobControl")
               JobDelete = @TRUE
               del DependJobList<i>
            end else
               if ErrMsg <> '' or (JobStarted and JobAborted) then
                  JobDelete = @TRUE
                  del DependJobList<i>
                  if ErrMsg <> '' then
                     if DebugSw then
                        LogMsg = "DependJob: ":DependJob:" removed from list because of error message below."
                        Call DSLogInfo(LogMsg, "JobControl")
                        LogMsg = "ErrMsg: ":ErrMsg
                        Call DSLogInfo(LogMsg, "JobControl")
                     end
                  end else
                     LogMsg = DependJob:": job aborted. ":FinishedDateTime:" in ":ElapsedTime:" seconds."
                     Call DSLogInfo(LogMsg, "JobControl")
                     if DebugSw then
                        LogMsg = "DependJob: ":DependJob:" removed from list because job aborted."
                        Call DSLogInfo(LogMsg, "JobControl")
                     end
                  end
               end
            end
         next i
         gosub GetMyRunTime
         if ExceededRunTime then
            ErrMsg = 'Error: ExceededRunTime ':ExceededRunTime:' seconds exceeded. ':SeqName:' job never finished.'
            gosub ErrRtn
            goto TheEnd
         end
         if not(JobDelete) and ContFlag then
            LogMsg = "Sleeping: ":SleepTime:" seconds."
            Call DSLogInfo(LogMsg, "JobControl")
            sleep SleepTime
         end
      repeat
* ------------------------------------------------------------
* main sequence finished
* ------------------------------------------------------------
      DependJob = SeqName
      gosub GetStartTime
      LogMsg = DependJob:": job finished. ":FinishedDateTime:" in ":ElapsedTime:" seconds."
      Call DSLogInfo(LogMsg, "JobControl")
      gosub RunJobsSub
      goto TheEnd
* ------------------------------------------------------------
GetMyRunTime:
      MyRunTime = (DATE() - MyStartDate) * 24 * 60 * 60
      MyRunTime += TIME() - MyStartTime
      if MyRunTime > MaxWaitTime then
         ExceededWaitTime = @TRUE
      end
      if MyRunTime > MaxRunTime then
         ExceededRunTime = @TRUE
      end
      return
* ------------------------------------------------------------
GetStartTime:
      ErrMsg = ''
      Cnt += 1
      read DependJobRec from DsJobs, DependJob then
         DependJobJobNo = DependJobRec<5>
         DependJobObjId = 'J\':DependJobJobNo:'\ROOT'
         read DependJobObjRec from DsJobObjects, DependJobObjId then
            JobType = DependJobObjRec<40>
            if JobType = '2' then
               IsSequence = @TRUE
            end else
               IsSequence = @FALSE
            end
            MutipleInstanceFlag = DependJobObjRec<59>
            if MutipleInstanceFlag <> 1 then

               * Call DSLogInfo("Getting row counts for ":DependJob, "JobControl")
* ------------------------------------------------------------
* Setup DSJobReportDb, run it, wait for it to finish, and test for success
* ------------------------------------------------------------
               hJob1 = DSAttachJob(DependJob, DSJ.ERRFATAL)
               If NOT(hJob1) Then
                  ErrMsg = "Error: Job Attach Failed: ":DependJob
                  gosub ErrRtn
                  goto SkipCheck
               End
* ------------------------------------------------------------
               JobAborted = @FALSE
               JobFinished = @FALSE
               LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
               Begin case
                  case LastRunStatus = DSJS.RUNNING
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Running in ":ChkSeqName
                  case LastRunStatus = DSJS.RUNOK
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Finished in ":ChkSeqName
                     JobFinished = @TRUE
                  case LastRunStatus = DSJS.RUNWARN
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Finished (see log) in ":ChkSeqName
                     JobsWithWarnings = DependJob
                     JobFinished = @TRUE
                  case LastRunStatus = DSJS.RUNFAILED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Aborted in ":ChkSeqName
                     JobAborted = @TRUE
                  case LastRunStatus = DSJS.CRASHED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Aborted in ":ChkSeqName
                     JobAborted = @TRUE
                  case LastRunStatus = DSJS.STOPPED
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Stopped in ":ChkSeqName
                     JobAborted = @TRUE
                  case @TRUE
                     LogMsg = DependJob:" Status: ":LastRunStatus:" Unknown in ":ChkSeqName
                     JobAborted = @TRUE
               end case
               if DebugSw then Call DSLogInfo(LogMsg, "JobControl")
               StartedDateTime = DSGetJobInfo(hJob1, DSJ.JOBSTARTTIMESTAMP)
               FinishedDateTime = DSGetJobInfo(hJob1, DSJ.JOBLASTTIMESTAMP)
               ElapsedTime = DSGetJobInfo(hJob1, DSJ.JOBELAPSED)
               if MainSeqName = DependJob then
                  MainStartedDateTime = StartedDateTime
                  MainSeqStatus = LastRunStatus
               end
               ErrCode = DSDetachJob(hJob1)
               LogMsg = LogMsg:" Started: ":StartedDateTime
               LogMsgs<-1> = Cnt:'. ':LogMsg
               if MainStartedDateTime <= StartedDateTime then
                  JobStarted = @TRUE
               end else
                  JobStarted = @FALSE
               end
            end else
               ErrMsg = 'Error: ':DependJob:' unable to get status 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
SkipCheck:
      return
* ------------------------------------------------------------
RunJobsSub:
      if GetRowCountsFlag then
* ------------------------------------------------------------
* Setup DSJobReportDbDriver.SeqName, run it, wait for it to finish, and test for success
* ------------------------------------------------------------
         hJob9 = DSAttachJob("DSJobReportDbDriver.":DependJob, DSJ.ERRFATAL)
         If NOT(hJob9) Then
            Call DSLogFatal("Job Attach Failed: DSJobReportDbDriver.":DependJob, "JobControl")
            Abort
         End Else
            LastRunStatus = DSGetJobInfo(hJob9, DSJ.JOBSTATUS)
            If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then
               Call DSLogInfo("DSJobReportDbDriver.":DependJob, "Reseting job and last run status")
               ErrCode = DSRunJob(hJob9, DSJ.RUNRESET)
               ErrCode = DSWaitForJob(hJob9)
               ErrCode = DSDetachJob(hJob9)
* ------------------------------------------------------------
               hJob9 = DSAttachJob("DSJobReportDbDriver.":DependJob, DSJ.ERRFATAL)
               If NOT(hJob9) Then
                  Call DSLogFatal("Job Attach Failed: ":"DSJobReportDbDriver.":DependJob, "JobControl")
                  Abort
               End
            End
         End
         ErrCode = DSSetParam(hJob9, "TargetDSN", TargetDSN)
         ErrCode = DSSetParam(hJob9, "TargetUser", TargetUser)
         ErrCode = DSSetParam(hJob9, "SeqName", DependJob)
         ErrCode = DSSetParam(hJob9, "EmailFlag", EmailFlag)
         ErrCode = DSSetParam(hJob9, "CheckParentFlag", "1")
         ErrCode = DSSetParam(hJob9, "EmailTo", EmailTo)
         ErrCode = DSSetParam(hJob9, "DebugSw", DebugSw)
         ErrCode = DSSetParam(hJob9, "FilePath", FilePath)
         ErrCode = DSSetParam(hJob9, "LogoPath", LogoPath)
         ErrCode = DSRunJob(hJob9, DSJ.RUNNORMAL)
         ErrCode = DSWaitForJob(hJob9)
         Status = DSGetJobInfo(hJob9, DSJ.JOBSTATUS)
         If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then
            * Fatal Error - No Return
            Call DSLogFatal("Job Failed: DSJobReportDbDriver.":DependJob, "JobControl")
         End
      end else
* ------------------------------------------------------------
* Run EtlChkSeqJobsNotRun here on main sequence
* ------------------------------------------------------------
         hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
         If NOT(hJob2) Then
            Call DSLogInfo("Job Attach Failed: EtlChkSeqJobsNotRun.":DependJob, "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.":DependJob, "Reseting job and last run status")
               ErrCode = DSRunJob(hJob2, DSJ.RUNRESET)
               ErrCode = DSWaitForJob(hJob2)
               ErrCode = DSDetachJob(hJob2)
* ------------------------------------------------------------
               hJob2 = DSAttachJob("EtlChkSeqJobsNotRun.":DependJob, DSJ.ERRFATAL)
               If NOT(hJob2) Then
                  Call DSLogFatal("Job Attach Failed: ":"EtlChkSeqJobsNotRun.":DependJob, "JobControl")
                  Abort
               End
            end
            ErrCode = DSSetParam(hJob2, "SeqName", DependJob)
            ErrCode = DSSetParam(hJob2, "EmailFlag", EmailFlag)
            ErrCode = DSSetParam(hJob2, "CheckParentFlag", '1')
            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.":DependJob, "JobControl")
            End
         End
      End
      return
* ------------------------------------------------------------
ErrRtn:
      Call DSLogInfo(ErrMsg , "JobControl")
      * Call DSLogFatal(ErrMsg, "JobControl")
      * abort
      return
* ------------------------------------------------------------
TheEnd: