Job Scheduler

Sort:
You are not authorized to post a reply.
Author
Messages
ImBack
Basic Member
Posts: 18
Basic Member
    I know that a particular person can  can view his/her own jobs in the job scheduler.  I also know that you can look at ALL jobs or at specific people.  I have a department head who wants to see all of the jobs under the payroll (PR) system code.  Is this possible?  Thanks a lot for checking.
    John Henley
    Senior Member
    Posts: 3347
    Senior Member
      Off top of my head, I don't think this would be possible within job scheduler, since jobs can have multiple steps, which can cross different modules. However, if I remember correctly, job scheduler and print manager only allow you to view tokens you have access to, so it might work that way if the department head only has access to payroll jobs... Another option would be to query against various job tables in GEN (QUEUEDJOB/JOBSTEP, etc.) and then copy/load payroll job reports to his print manager...? I guess my real question would be ... what is the business case?
      Thanks for using the LawsonGuru.com forums!
      John
      ImBack
      Basic Member
      Posts: 18
      Basic Member
        This particular department head is a micro-manager type who wants to know about ANY jobs in waiting or any jobs that have been running for more than say 3 hours.  I've thought about the QUEUEDJOB table and doing queries against it but the easiest approach would be if they could just go into the jobscheduler themselves and see.  We don't want her to have access to ALL jobs, only those under the PR system code.
        TBonney
        Veteran Member
        Posts: 277
        Veteran Member
          We run Windows/SQL and have a process that monitors the QUEUEDJOB table for waiting jobs and notifies certain personnel by email based on what jobs are waiting. We do this by way of a batch job, a SQL query and a series of vbscripts (one for each receiving department.

          You could employ a similar process and modify the SQL query to only identify the PR jobs by using TOKEN LIKE 'PR%' as part of your selection criteria.

          Just let me know if you'd like more details...tbonney@mvnhealth.com.
          ImBack
          Basic Member
          Posts: 18
          Basic Member
            Thank you very much for your help. I will see if I can get that to work.
            ImBack
            Basic Member
            Posts: 18
            Basic Member
              I've gotten the queries to work fine. Thanks! Now, as a secondary part of this, I need to identify jobs that have been running for more than 2 hours. Unfortunately, the QUEUEDJOBS table only has ACTSTARTDATE and ACTSTARTTIME but no elapsed time as you can see in the job scheduler. Does anyone know where it might be stored or how I can derive it?
              Greg Moeller
              Veteran Member
              Posts: 1495
              Veteran Member
                Why not take the ACTSTARTTIME compare it to the system time, and (provided) the job is still running (jqstatus -a) base your calculation off of that? Would that not work?
                ImBack
                Basic Member
                Posts: 18
                Basic Member
                  Thanks for answering. I have used a derivative of what you said and came up with the query below.

                  SELECT JOBNAME, ACTSTARTTIME, (TO_CHAR(SYSDATE, 'HH24MISS')),
                  FROM LAWGEN9.QUEUEDJOB
                  WHERE (R_STATUS IN ('00'))
                  AND (TO_CHAR(SYSDATE, 'HH24MISS') - ACTSTARTTIME > 20000)

                  --AND (ABS(ACTSTARTTIME - TO_CHAR(SYSDATE, 'HH24MISS') > 20000))

                  The problem I have now is that the query works fine as shown but when I substitute the line below it (commented out) for the line shown, I get a missing right parenthesis error for some reason. I have looked at this for an hour now and can't seem to see where the issue is because the paren count is fine. I have to use absolute value in the event I end up with a negative number.
                  John Henley
                  Senior Member
                  Posts: 3347
                  Senior Member
                    that's because your parentheses were off I think...
                    should be:
                    AND (ABS(ACTSTARTTIME - TO_CHAR(SYSDATE, 'HH24MISS')) > 20000)
                    Thanks for using the LawsonGuru.com forums!
                    John
                    Ryan Speight
                    Basic Member
                    Posts: 5
                    Basic Member
                      I think you mean --AND (ABS(ACTSTARTTIME - TO_CHAR(SYSDATE, 'HH24MISS')) > 20000)
                      ImBack
                      Basic Member
                      Posts: 18
                      Basic Member
                        PERFECT!! Thanks a lot guys. I need a new set of eyes!
                        Chesca
                        Veteran Member
                        Posts: 490
                        Veteran Member
                          Would you be willing to share your SQL please? I have been trying to create script to monitor the jobs but it keeps going into needs recovery for some reason.
                          You are not authorized to post a reply.