Querying Tidal for Job Status

The need to look to the future for what is going to run and easily make a report does not appear possible through the Tidal Scheduler interface. Here is what I wrote to meet this need:

use Admiral

select — top 100
case conn.nodmst_type
when 1 then ‘Master’
when 6 then ‘Windows’
when 11 then ‘SQL’
else ‘Type not yet decyphered’
end as agent_type,
conn.nodmst_name as agent_name,
jm.jobmst_name,
case jr.jobrun_status
when 0 then ‘Scheduled’
when 1 then ‘Waiting On Dependencies’
when 2 then ‘Waiting On Operator’
when 3 then ‘Held’
when 5 then ‘Timed Out For Day’
when 7 then ‘Agent Unavailable’
when 8 then ‘Agent Disabled’
when 10 then ‘Waiting on Group’
when 11 then ‘Waiting On Children’
when 49 then ‘Waiting On Resource’
when 50 then ‘Launched’
when 51 then ‘Active’
when 52 then ‘Stopped’
when 53 then ‘Deferred’
when 66 then ‘Error Occurred’
— Am not joining back to underlying table because 97 shows up incorrectly as Completed Normally
when 97 then ‘Completed Normally (Gathering Output)’
when 98 then ‘Completed Abnormally’
when 99 then ‘Externally Defined’
when 101 then ‘Completed Normally’
when 103 then ‘Completed Abnormally’
when 104 then ‘Skipped’
when 105 then ‘Orphaned’
when 106 then ‘Aborted’
when 107 then ‘Externally Defined’
when 108 then ‘Timed Out’
when 109 then ‘Cancelled’
end as jobrun_status_desc,
–jr.jobrun_proddt as run_date, — this field appears to be like jobrun_esttime but with less precision

jr.jobrun_launchtm,
jr.jobrun_lstchgtm,
DATEDIFF(mi,jobrun_launchtm,jobrun_lstchgtm) as run_duration,
conn.nodmst_alias,
conn.nodmst_name,
jr.jobrun_fromtm,
jr.jobrun_untiltm /*,
u.usrmst_name */
from jobrun jr
join jobmst jm on jr.jobmst_id=jm.jobmst_id
join nodmst conn on jr.nodmst_id=conn.nodmst_id
— join usrmst u on conn.nodmst_user=u.usrmst_id –conn.usrmst_id=u.usrmst_id
where
jm.jobmst_name not like ‘%<<some job name>>%’ and
JM.JOBmst_name like ‘%<<some job name>>%’ and
–jr.jobrun_esttime<GETDATE() and
–jm.jobmst_type in (8,2) and — 1=group; 2=job; 8=sql job
jm.jobmst_type in (8) and — 1=group; 2=job; 8=sql job
–jr.jobrun_status=97 and — 97=completed normally (gathering output)
–jr.jobrun_esttime between ‘20121022’ and ‘20121024’ and
jr.jobrun_status in (
–0, — Scheduled
1, — Waiting On Dependencies
2, — Waiting On Operator
3, — Held
5, — Timed Out For Day
7, — Agent Unavailable
8, — Agent Disabled
10, — Waiting on Group
11, — Waiting On Children
49, — Waiting On Resource
50, — Launched
51, — Active
52, — Stopped
53, — Deferred
66, — Error Occurred
97, — Completed Normally (Gathering Output)
98, — Completed Abnormally
99, — Externally Defined
101, — Completed Normally
103, — Completed Abnormally
104, — Skipped
105, — Orphaned
106, — Aborted
107, — Externally Defined
108, — Timed Out
109 — Cancelled
)
order by
— jr.jobrun_esttime desc
— DATEDIFF(mi,jobrun_launchtm,jobrun_lstchgtm) desc
–jr.jobrun_launchtm
JR.jobrun_lstchgtm

This entry was posted in SQL, Tidal Enterprise Scheduler. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *