Prev
Next
Forums Infor / Lawson Platforms S3 Systems Administration

Evaluation of run time in QUEUEDJOB table

 4 Replies
 2 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
TBonney
Veteran Member
Posts: 281
Veteran Member
3/21/2022 3:53 PM

    For use within SQL queries, how do folks evaluate the total run time of jobs in the QUEUEDJOB table, since the STARTTIME & STOPTIME fields are formatted as (decimal(6,0),not null), instead of date/time format?

    We are trying to write a query to identify "long-running" jobs, in the active job queue by comparing the STARTTIME to GETDATE() tme stamp, but not sure how to convert the STARTDATE formatting in order to be able to successfully compare the two.

    Any advice would be appreciated. Thank you in advance.

    Greg Moeller
    Veteran Member
    Posts: 1498
    Veteran Member
    3/21/2022 4:09 PM
      I'm not sure who gave me this query, so I can't give proper credit.. but here's what we've used:
      SELECT USERNAME,JOBNAME,TOKEN,ACTSTARTDATE,ACTSTARTTIME,
      CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) as time) as 'Start HH:mm:ss',
      STOPTIME,
      CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) as time) as 'Stop HH:mm:ss',
      CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) AS TIME), CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) AS TIME)), ''), 114) AS 'RunTime Duration'
      FROM gen.dbo.QUEUEDJOB
      WHERE STATUS = '63' AND CONVERT(VARCHAR(21),STOPDATE,101) = CONVERT(VARCHAR(21),GETDATE(),101)
      -- ORDER BY 'RunTime Duration' desc
      ORDER BY ACTSTARTDATE desc, ACTSTARTTIME desc
      TBonney
      Veteran Member
      Posts: 281
      Veteran Member
      3/21/2022 5:27 PM
        Greg,

        Thanks so much. Although you say you can't give proper credit to the original author, I can certainly give you the credit. Thanks for the help! That 'Run Time Duration' column is exactly what I was looking for.
        Greg Moeller
        Veteran Member
        Posts: 1498
        Veteran Member
        3/21/2022 5:34 PM

          You are welcome!

          Glad I was able to help out.

          LarryD
          Basic Member
          Posts: 7
          Basic Member
          4/2/2022 8:33 PM

            In case anyone is interested in an Oracle solution to this issue. The first trick is to convert the DATE and TIME columns to character strings, concatenate them, then convert them back to dates before subtracting them to get the duration. I had this part a few years ago. The problem was converting the result back to a character string that had just the hours, minutes, seconds. I received various errors depending on what I tried.
            I then found an example on the Internet where the 'Epoch time' of Jan 1 1970 was added to the difference.
            Since Oracle stores DATE datatypes as the number of seconds since epoch, adding that date is essentially adding zero. However, this seems to perform an implicit converstion of the result to the character format used for the epoch value. Just make sure that its time portion contains the format that you want in your final result. Greg's solution uses the MSSQL Date style of 114. If you want fractional seconds like it has, in Oracle you will need to convert to TIMESTAMP vs DATE.

            SELECT USERNAME, JOBNAME, TOKEN, ACTSTARTDATE, ACTSTARTTIME, STOPDATE, STOPTIME,
            to_char(
            to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' +
            ( to_date(to_char(STOPDATE,'mmddyyyy'||to_char(STOPTIME,'099999','mmddyyyyhh24miss'
            - to_date(to_char(ACTSTARTDATE,'mmddyyyy'||(to_char(ACTSTARTTIME,'099999' ),'mmddyyyyhh24miss'
            )
            ,'hh24:mi:ss' as "RunTime Duration"
            from gen.queuedjob
            --your optional WHERE clause here
            order by actstartdate desc ;