Batch job monitoring with ProcessFlow

We have a flow that does this.  but, we are using the Lawson query node and not SQL.

 

PROD=GEN&FILE=QUEUEDJOB&FIELD=JOBNUMBER;STATUS;JOBNAME;USERNAME;JOBQUEUE;TOKEN;ACTSTARTDATE;ACTSTARTTIME&SELECT=STATUS=34|STATUS=35&OUT=CSV&DELIM=~

 

Hi Karen, Thanks for the info. Question - What do STATUS values 34, 35 mean? What is value of STATUS when job completed successfully? Thanks.

I was looking for documentation but not finding it. One of them is "needs recovery" and the other is "invalid parameters". We are on Oracle and I checked the field definition. It is "raw". To use it in SQL you'll need to CAST it. I think I have the number of characters right. CAST(r_status AS VARCHAR(4))

Found STATUS codes and translations. See KB 1197107 on Inforxtreme.com. Thanks Karen for your assistance. Have a great day! STATUS value & Translations (There's more statuses. Here's 2 examples.) 0 "running" (0 is Zero) 63 "normal completion"

Here is the SQL we use in our processflow which runs once an hour and then alerts, recovers or deletes the batch job based on status. SELECT (CONVERT(VARCHAR(10),(DATEDIFF(SECOND,ACTSTARTDATE+CONVERT(VARCHAR(10),STUFF(STUFF(FORMAT(ACTSTARTTIME,'000000'),3,0,':'),6,0,':'),108),GETDATE())/86400))+' Days ' +CONVERT(VARCHAR(10),((DATEDIFF(SECOND,ACTSTARTDATE+CONVERT(VARCHAR(10),STUFF(STUFF(FORMAT(ACTSTARTTIME,'000000'),3,0,':'),6,0,':'),108),GETDATE())%86400)/3600))+' Hours ' +CONVERT(VARCHAR(10),(((DATEDIFF(SECOND,ACTSTARTDATE+CONVERT(VARCHAR(10),STUFF(STUFF(FORMAT(ACTSTARTTIME,'000000'),3,0,':'),6,0,':'),108),GETDATE())%86400)%3600)/60))+' Minutes ' +CONVERT(VARCHAR(10),(((DATEDIFF(SECOND,ACTSTARTDATE+CONVERT(VARCHAR(10),STUFF(STUFF(FORMAT(ACTSTARTTIME,'000000'),3,0,':'),6,0,':'),108),GETDATE())%86400)%3600)%60))+' Seconds ') AS "TimeRunning", Q.JOBNUMBER, Q.USERNAME, LTRIM(RTRIM(Q.JOBNAME)) AS JOBNAME, Q.STATUS, CASE WHEN Q.STATUS = '0' THEN 'Running' WHEN Q.STATUS = '30' THEN 'Waiting' WHEN Q.STATUS = '31' THEN 'Waiting Step' WHEN Q.STATUS = '32' THEN 'Waiting On Time' WHEN Q.STATUS = '33' THEN 'Waiting Recovery' WHEN Q.STATUS = '34' THEN 'Needs Recovery' WHEN Q.STATUS = '35' THEN 'Invalid Parameters' WHEN Q.STATUS = '36' THEN 'Queue Inactive' WHEN Q.STATUS = '37' THEN 'On Hold' WHEN Q.STATUS = '60' THEN 'Recurring Skipped' WHEN Q.STATUS = '61' THEN 'Recovery Deleted' WHEN Q.STATUS = '62' THEN 'Cancelled' WHEN Q.STATUS = '63' THEN 'Normal Completion' WHEN Q.STATUS = '80' THEN 'Control Record' ELSE 'Unknown' END AS TextStatus, CASE WHEN Q.STATUS = '33' THEN 'Recover' WHEN Q.STATUS = '34' THEN 'Recover' WHEN Q.STATUS = '35' THEN 'Delete' WHEN Q.STATUS = '36' THEN 'Alert' WHEN Q.STATUS = '37' THEN 'Alert' ELSE 'Alert' END AS ActionGroup, (Q.RSTSTEPNBR+1) AS "JobStepNbr", Q.ACTSTARTDATE, Q.ACTSTARTTIME, Q.STOPDATE, Q.STOPTIME, Q.TOKEN, Q.PROCESSID, Q.QJBSET10_SS_SW, Q.QJBSET12_SS_SW, Q.QJBSET6_SS_SW, Q.QJBSET7_SS_SW, Q.QJBSET8_SS_SW, Q.QJBSET9_SS_SW FROM Lawson_GEN.dbo.QUEUEDJOB Q WHERE Q.STATUS NOT IN ('0','30','31','32','60','61','62','63','80') ORDER BY TimeRunning DESC;

We use a webrun node and for the "Web program" we run /lawson-ios/action/ListQueuedJobs?filter=jobStatus%20EQ%20needsRecovery This can be used to pull any status you want to look for.

Good stuff to know. Thanks to all!