what are you trying to do w/ this data ? if you have bdssa i think this information may be available there as well.
We're trying to maximize server utilization and response by identifying, optimizing and eliminating jobs and schedules which impact system response and resources (memory, job threads, etc), causing performance issues (multiple job execution hotspots, intervals less than four hours, etc) - and produce "bad neighbor" reports/notifications, and alerts to netcool/ITM advising when our thresholds have been exceeded.
if you are looking to actually monitor what's happening realtime you should be looking at the jmx interface to the appserver to see what's going on at any given time in the appserver. really the number of jobs is not relevant. what matters is the workitem thread usage and what those jobs are doing. you could have 100 jobs running but still have work item threads available, or you could have 1 job running and no work item threads available (meaning no other job will be able to actually do anything). it would make more sense i think to start graphing that to see if/when you have problem times.
it would make sense to look at what jobs are running during a given time. so here's one query for that which covers a couple time ranges.
WITH temp1 (group_id, path) AS
select group_id, cast(name as varchar(1000))
from blgroup bg1
where parent_group_id = 0
cast(bg2.path ||'/'|| cast(bg1.name as varchar(100)) as varchar(1000))
from blgroup bg1
inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id
select temp1.path ||'/'|| j.name,o.name,jr.job_run_id,to_char(jr.start_time,'YYYY-MM-DD HH24:MI:SS'),to_char(jr.end_time,'YYYY-MM-DD HH24:MI:SS'),ap.display_name from temp1
inner join job j on temp1.group_id = j.group_id
join job_run jr on j.job_id = jr.job_id and j.job_version_id = jr.job_version_id
join object_type o on j.object_type_id = o.object_type_id
join application_server ap on jr.application_server_id = ap.application_server_id
where j.is_saved_explicitly = 1
(jr.end_time > to_date('2014-01-01 00:00:00",'YYYY-MM-DD HH24:MI:SS') and jr.start_time < to_date('2014-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'))
(jr.end_time > to_date('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and jr.start_time < to_date('2014-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'))
i think for the job schedules you can use this from the sqlmap.properties. you could add the logic to get the path to the job to that.
SELECT j.name, j.object_type_id, s.schedule_id, s.created_by_user_id, s.created_by_role_id, s.modified_by_user_id, \
s.modified_by_role_id, s.frequency_type_id, s.job_id, s.execution_task_id, \
s.once_date_time, s.days_of_week, s.day_of_month, s.frequency, \
s.recurring_time, s.recurring_time_zone_id, s.start_time, s.interval_day, \
s.interval_hour, s.interval_minute, s.scheduler_type_id, s.next_run_time, \
s.last_run_time, s.max_time_in_sched_q, s.locale_id, s.scheduler_lock_id, \
s.date_created, s.date_modified, s.is_deleted, s.approval_request_id, \
s.approval_status_id, s.job_priority_id, s.transaction_id \
FROM schedule s INNER JOIN job j ON s.job_id = j.job_id \
WHERE s.is_deleted = 0 \
and s.next_run_time is not null \
and j.object_type_id != 7036 \
and j.is_deleted = 0 \
and j.is_latest_version = 1 \
and not exists \
(SELECT 1 FROM batch_job_job INNER JOIN job ON batch_job_job.batch_job_id = job.job_id \
WHERE batch_job_job.job_id = s.job_id AND ((job.object_type_id = 30 AND job.is_deleted = 1 AND job.is_latest_version = 1) OR \
job.object_type_id = 7036));