PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 10/02/2019 2:22 PM by  Scooter
Batch job monitoring with ProcessFlow
 7 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Scooter
Private
Private
Veteran Member
(129 points)
Veteran Member
Posts:59


Send Message:

--
10/02/2019 11:05 AM

    I'm creating a processflow to monitor Batch jobs completion. 

    Using SQL node to query "GEN" table QUEUEDJOB.   

    When testing query, field STATUS returns values like [B@f106b0 

    First, is this correct table to monitor batch jobs? 

    If yes, how can we determine if job went to recovery or completed successfully when

               STATUS has values like [B@f106b0 ?    What am I missing? 

     

    Below job PA102 completed successfully but STATUS returns unknown value [B@1bc6b3a  

    What does [B@1bc6b3a mean?

    Here's our test SQL looking for job PA102 with SQL results:

    Success executing SQL:
    SELECT    USERNAME,   JOBNAME,   R_STATUS
        FROM   GEN.QUEUEDJOB
        WHERE   JOBNAME = 'PA102'


    Number of rows returned: 1

    USERNAME, JOBNAME, R_STATUS
    u0003035  ,  PA102     ,  [B@1bc6b3a


    Thanks for your replies and assistance.

    Karen Sheridan
    Private
    Private
    Veteran Member
    (341 points)
    Veteran Member
    Posts:129


    Send Message:

    --
    10/02/2019 11:14 AM

    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=~

     

    Scooter
    Private
    Private
    Veteran Member
    (129 points)
    Veteran Member
    Posts:59


    Send Message:

    --
    10/02/2019 11:50 AM
    Hi Karen,
    Thanks for the info. Question - What do STATUS values 34, 35 mean? What is value of STATUS when job completed successfully? Thanks.
    Karen Sheridan
    Private
    Private
    Veteran Member
    (341 points)
    Veteran Member
    Posts:129


    Send Message:

    --
    10/02/2019 12:22 PM
    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))
    Scooter
    Private
    Private
    Veteran Member
    (129 points)
    Veteran Member
    Posts:59


    Send Message:

    --
    10/02/2019 12:40 PM
    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"
    SWilkins
    Admin Systems Architect
    Phoenix Children's Hospital
    Advanced Member
    (67 points)
    Advanced Member
    Posts:25


    Send Message:

    --
    10/02/2019 12:47 PM
    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;
    Dave Curtis
    Manager, HRIS
    University of Maryland Medical System
    Veteran Member
    (392 points)
    Veteran Member
    Posts:136


    Send Message:

    --
    10/02/2019 1:03 PM
    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.
    Scooter
    Private
    Private
    Veteran Member
    (129 points)
    Veteran Member
    Posts:59


    Send Message:

    --
    10/02/2019 2:22 PM
    Good stuff to know. Thanks to all!
    You are not authorized to post a reply.