TIDAL: calculating how many minutes between "waiting on resource" and "active"

[Steve Atwood]

TES 6.1.0.656.  Oracle db.

Hi Everyone,

We use the Tidal "Virtual Resource" (VR) to manage the tape drive availability for all scheduled jobs that require those resources.

This works fine, but I'm having trouble reporting on one metric: I need to generate a list of all jobs that have a dependency on a particular VR that also get stuck in a status of 'Waiting on Resource' for longer than X minutes. In other words we need to highlight the tape libraries that cannot handle the load.

I know how to extract the exact time(s) a job in a daily schedule goes into 'Resource Wait' and how to extract the time(s) it goes 'Active'.

But I can't figure out how to calculate the difference between those two datetime stamps, in terms of minutes, and exclude them from the final results.

I want to discard from consideration any job that goes 'Active' within 5 minutes of going into 'Resource Wait'.

Here's my query so far..including a comment prefaced with "-- needed code here" shown in bold font  what I'm asking of this forum.

Thanks!

SELECT m.jobrun_id "JOBID"
,(case when m.msglog_text like '%Resource%' then 'WAIT' when m.msglog_text like '%Active%' then 'LAUNCH' end) "STATUS"
,to_char(m.msglog_crtdt,'MM-DD-YYYY HH24:mi:ss') "AT"
,j.jobmst_name "JOB"
,r.resmst_name "RESOURCE"
FROM tidal.msglog M
join tidal.nodmst n ON n.NODMST_ID = m.NODMST_ID
join tidal.jobmst j ON j.jobmst_id=m.jobmst_id
join tidal.owner o ON o.owner_id=j.jobmst_owner
join tidal.jobdtl d ON D.JOBDTL_ID = J.JOBDTL_ID
join tidal.jobrun r on r.jobrun_id=m.jobrun_id
join tidal.usrmst u on u.USRMST_ID=d.jobdtl_proxy
join tidal.jobcls c on c.jobcls_id=j.jobcls_id
left join tidal.resjob s on s.jobmst_id=j.jobmst_id
left join tidal.resmst r on r.resmst_id=s.resmst_id
WHERE j.jobmst_type=2
and r.resmst_name like '%3584 LM%'
and j.jobmst_name in
(SELECT j.jobmst_name
FROM tidal.jobmst j
join tidal.resjob s on s.jobmst_id=j.jobmst_id
join tidal.resmst r on r.resmst_id=s.resmst_id
left outer join tidal.jobdtl d on D.JOBDTL_ID = J.JOBDTL_ID
where j.jobmst_type=2
and r.resmst_name like '%3584 LM%'
AND j.jobmst_dirty<>'X'
and m.jobrun_id in (select jobrun_id from tidal.msglog where msglog_text like '%Active%')
and m.jobrun_id in (select jobrun_id from tidal.msglog where msglog_text like '%Resource%')
and (m.msglog_text like '%Active%' or m.msglog_text like '%Resource%'))
--needed code here: and (datetimestamp of job going 'Active' minus datetimestamp of job going to 'Waiting on Resource' ) > 5 minutes
group by m.jobrun_id,m.msglog_text,m.msglog_crtdt,j.jobmst_name,r.resmst_name
order by "AT" desc;

[Derrick Au]

Hi Steve

From an Oracle syntax perspective ('Waiting on Resource' datetime stamp - 'Active' datetime stamp) * 24 * 60 to get the time difference in minutes :-) But the above approach requires a bit more than just a SELECT query. When a job becomes 'Active' the datetime stamp gets written into the msglog table; and when a job enters 'Waiting on Resource' state, the datetime stamp gets written into the msglog table. You will have to pull in other pieces of data--the msglog_id and msglog_crtdt in order to get the datetime stamps. You may be better off writing a stored procedure and declaring variables for these intermediary values, then perform the necessary computation

Before throwing in the towel, let's approach this problem from a different angle. There are X number of jobs that are dependent on a particular virtual resource. When a virtual resource gets used, then Y number of jobs are 'Waiting on Resource' -- so what we really need is to determine the ELAPSED TIME between the datetime stamp when jobrun status changes to 'Waiting on Resource' AND datetime stamp of the current time for jobs dependent on that virtual resource

To determine what virtual resources are currently used, run the following:

select resmst.resmst_name, reslock.* from reslock
join resmst on resmst.resmst_id = reslock.resmst_id

Jot down a resource name because we will need it again later

select jobmst.jobmst_prntname,
jobmst.jobmst_name,
resmst.resmst_name,
reslock.reslock_locked,
jobrun.jobrun_proddt,
jobrun.jobrun_time,
case jobrun.jobrun_status
when 49 then 'JOB_STATUS_READY'
when 51 then 'JOB_STATUS_ACTIVE'
end as 'Status',
jobrun.jobrun_lstchgtm,
case
when reslock.reslock_locked is not null then ' '
else
datediff(minute, jobrun.jobrun_lstchgtm, getdate())
end as 'Elapsed Time',
resjob.*
from resjob
join jobmst on jobmst.jobmst_id = resjob.jobmst_id
join resmst on resmst.resmst_id = resjob.resmst_id
join jobrun on jobrun.jobmst_id = jobmst.jobmst_id
left outer join reslock on reslock.jobrun_id = jobrun.jobrun_id
where resmst.resmst_name = 'enter a resource name'
and jobrun.jobrun_proddt = dateadd(dd, 0, datediff(dd, 0, getdate()))
and (jobrun.jobrun_status = 49 or jobrun.jobrun_status = 51) -- 49 = job status ready means job is waiting on resource, and 51 = job status active means job is currently running
and datediff(minute, jobrun.jobrun_lstchgtm, getdate()) > 5  -- report back jobs if the time difference between when the jobrun status last changed datetime and current datetime is greater than 5 minutes

I will leave the above MS SQL snippets to the Oracle experts for translation :-)

BR,

Derrick Au

[Steve Atwood]

Thanks Derrick,

I'm already running a different query in "real time" to alert on any jobs that are stuck in resource wait longer than 5 minutes.

However, what I'm looking to do here is compile a listing well after all of the daily jobs have finished, so the jobrun table would be useless to me here.  My new query can identify all jobs, by the jobrun_id field in the msglog table, so that is half the battle.  Just need to weed out those that waited less than 5 minutes for the resources in question...and I was hoping to find a shortcut to accomplish that part.

Thanks again for your response, but I think you're right that I will need to create a stored procedure with a couple of variables to make it to the finish line here.

Regards,

-Steve