Pavel

daily autosys metrics

Discussion created by Pavel on Feb 10, 2011
I'm looking for some rough numbers demonstrating autosys performance for entire day. Idea is to put these numbers in monthly trend graphs.
I came up with the following queries.
Can you try it on your side and tell me if they make sense. thanks, Pavel


[font=Courier New]


DAY RUNS LAG
----------- ----------- -----------
Feb 3 2011 51886 15


-- Oracle query -----------------------
SELECT to_char(sysdate-1,'Mon dd yyyy') as Day,
count(p1.joid) as Runs,
round(avg((p2.que_status_stamp - p1.stamp)*86400)) as Lag
FROM ujo_proc_event p1, ujo_proc_event p2
WHERE p1.joid = p2.joid
AND p1.stamp < (select to_char(sysdate, 'dd-mon-yyyy') from dual)
AND p1.stamp > (select to_char(sysdate-1,'dd-mon-yyyy') from dual)
AND p1.status = (select code from ujo_intcodes where text ='STARTING')
AND p2.status = (select code from ujo_intcodes where text ='RUNNING')
AND p1.run_num = p2.run_num
AND p1.ntry = p2.ntry
/

-- Sybase query ------------------------
SELECT convert(char(11),dateadd(hh,-24,getdate())) as DAY,
count(p1.joid) as RUNS,
avg( datediff(ss, p1.stamp,p2.que_status_stamp)) as LAG
FROM ujo_proc_event p1, ujo_proc_event p2
WHERE p1.stamp < convert(char(11),getdate())
AND p1.stamp > convert(char(11),dateadd(hh,-24,getdate()))
AND p1.status = (select code from ujo_intcodes where text ='STARTING')
AND p2.status = (select code from ujo_intcodes where text ='RUNNING')
and p1.joid = p2.joid
AND p1.run_num = p2.run_num
AND p1.ntry = p2.ntry
go

















[font]

Outcomes