Job Scheduler

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?

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.

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.

Thank you very much for your help. I will see if I can get that to work.

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?

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?

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.

that's because your parentheses were off I think...
should be:
AND (ABS(ACTSTARTTIME - TO_CHAR(SYSDATE, 'HH24MISS')) > 20000)

I think you mean --AND (ABS(ACTSTARTTIME - TO_CHAR(SYSDATE, 'HH24MISS')) > 20000)

PERFECT!! Thanks a lot guys. I need a new set of eyes!

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.