Is there a way to get a list of tasks inside a schedule programmatically?
Ideally without exporting the object and checking via shell script, or java. I'm hoping a combination of a clever SQL call and a little scripting.
I'm not quite seeing anything in the OSA table that will help, but that seems like where it would be.
Credit to petwir I believe is where I got this from. Here is what I use in our environment quite often (oracle):
select oh_name as SCHEDULE , jpp_object as OBJECT , JPP_ErlstStTime as STARTTIME , jppc_calekeyname as CALENDARfrom oh inner join jpp on oh_idnr = jpp_oh_idnr left outer join jppc on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnrwhere oh_name = 'JSCH.NAME.HERE'order by 1,2,3,4
Wow, that's really helpful! Thanks!
If you don't mind, can I ask you another? Is there an easy way to pull the turnaround time for a certain schedule?
I don't know about "easy".
Now that you have the list of objects that are in your schedule, you could return the min(start time) and max(end time) for a given day from the statistics table for that list of objects. Those statistics are stored in the AH table. Column ah_timestamp2 contains the start time, and column ah_timestamp3 contains the end time, both stored in GMT. The SQL could compute the difference between the min() and max(), thus giving you "turnaround time" for that day.
MatthiasSchelp has provided the appropriate query for returning the litteral "turnaround time" for a schedule object. I was thinking your requirement was more like "how long does it take for the objects in the schedule to run". I suspect Matthias's SQL is what you really want?
That's an easy one ...
select TO_CHAR(OSA_STARTTIME,'HH24:MI:SS') from OSA;
Pete: I see what you're thinking. That would be a good way to look at statistics!
Matthais: Thanks, that's exactly it!