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!