Evidently I can go in web administrator and look up the schedule for each job.
But what I would like is a report, that shows all the job, the schedule names and when they run.
It could be a metadata report, but I have not found one.
Maybe I can do a universe on teh reporitory, but that seems roundabout to me.
We still run the old version 6 on our production server, testing 11, but it takes a lot of time.
slowbsure (BOB member since 2004-09-27)
Will be implemented in 11.7
Werner Daehn 
(BOB member since 2004-12-17)
Is this functionality available in 11.7 now ?
if not, then any other way to get the report on future schedule runs ?
whysosoft (BOB member since 2005-04-08)
it is.
Werner Daehn (BOB member since 2004-12-17)
Thats good.
where to go and how to get the report ?
whysosoft (BOB member since 2005-04-08)
Under WebAdmin, Batch Jobs, there is not only the tab for looking at the logs or executing/scheduling jobs, there is now another tab for schedules.
Werner Daehn (BOB member since 2004-12-17)
That section lists me the schedules…Thanks
What i would like to have is, the report should show DI job schedule for the foreseeable future e.g the coming one month of DI job schedules containing job names and job schedule run dates etc
whysosoft (BOB member since 2005-04-08)
We list the next execution time for each job in that report, don’t we?
Werner Daehn (BOB member since 2004-12-17)
No. The only columns shown there are Schedule(name), jobServer, job, system configuration,Active and Repository Name.
When we click the Schedule(name), we get that schedule’s configuration parameters.
whysosoft (BOB member since 2005-04-08)
File an enhancement request please. The code is there, we just had discussions what fields to show without overloading the dialog.
Werner Daehn (BOB member since 2004-12-17)
what I understand the new feature is to show the next execution time, but it does not show the execution pattern? for example, a job maybe scheduled to run every Monday and Friday, as of Sunday the next execution time would be the coming Monday but it does not inform about Friday. In order to get a month worth of data, we need to know the execution pattern rather than just the next execution time.
what is the code about and is the code able to give the execution pattern?
adwait (BOB member since 2005-12-08)
Exactly. It shows the next execution. The information you requested is available, just query the AL_SCHED_INFO table and see the pattern bitmap.
To get this as a report, please file an enhancement request - the feature would be very simple to implement.
Werner Daehn (BOB member since 2004-12-17)
Thanks. I am aware that recurrence_pattern can be used to calculate the information I want. Just to clarify, for the enchancement request, are you able to implement the next execution time as a report, or are you able to show the pattern information as a report as well?? any ideas on the time frame of the implementation?
adwait (BOB member since 2005-12-08)
I was thinking about adding it as a new column in the existing report.
“pattern: daily at 08:00”
The implementation cost is low as the information itself exists already. It is just a matter of priorities. Go to support and tell them why you cannot live without and it should get into the next service pack.
Werner Daehn (BOB member since 2004-12-17)
Werner,
Is there any repository query that I can manually issue to the repository DB and see which jobs are scheduled and when?
regards
Prem
biexplorer 
(BOB member since 2007-06-21)
The AL_SCHED_INFO table holds that information, a little bit encoded though.
Werner Daehn (BOB member since 2004-12-17)
Hi Werner,
Thanks for the reply. I knew that. But when i look into the table, I dont see enough info like name of job, etc. I see some encoded value for a column. (am away from office and dont remember it now).
Is there a query that can give me
regards
Prem
biexplorer (BOB member since 2007-06-21)
Yes, certainly because webadmin does just read the repo either. I don’t know the details though. But frequency for example we had a discussion in this board a couple of years ago.
Werner Daehn (BOB member since 2004-12-17)
HI,
This query gives some information about scheduling. but it doesnt help when I need a daily schedule or a hourly schedule.
Would like to know if anyone has a better query.
/* Formatted on 2007/12/12 15:19 (Formatter Plus v4.8.7) */
SELECT UPPER (al_lang.NAME) AS jobname,
UPPER (al_sched_info.sched_name) AS schedulename,
TO_CHAR(al_sched_info.start_time, 'HH24:Mi:Ss') AS starttime,
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 0))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Mon",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 1))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Tue",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 2))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Wed",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 3))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Thu",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 4))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Fri",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 5))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Sat",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 6))
) > 0
AND recurrence_type = 'WEEKLY'
THEN 'X'
ELSE ''
END "Sun",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 0))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "01",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 1))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "02",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 2))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "03",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 3))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "04",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 4))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "05",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 5))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "06",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 6))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "07",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 7))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "08",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 8))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "09",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 9))
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "10",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 10)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "11",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 11)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "12",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 12)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "13",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 13)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "14",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 14)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "15",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 15)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "16",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 16)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "17",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 17)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "18",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 18)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "19",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 19)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "20",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 20)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "21",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 21)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "22",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 22)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "23",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 23)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "24",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 24)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "25",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 25)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "26",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 26)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "27",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 27)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "28",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 28)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "29",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 29)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "30",
CASE
WHEN BITAND ((POWER (2, 31) + recurrence_pattern),
(POWER (2, 30)
)
) > 0
AND recurrence_type = 'MONTHLY'
THEN 'X'
ELSE ''
END "31",
al_sched_info.*
FROM di_edw.al_lang al_lang FULL OUTER JOIN di_edw.al_sched_info al_sched_info
ON al_lang.guid = al_sched_info.job_guid
WHERE active = 1
AND al_lang.object_type = 0
AND TYPE = 0
AND NAME != 'CD_JOB_d0cafae2'
AND NAME != 'di_job_al_mach_info'
AND al_lang.object_key =
(SELECT MAX (object_key)
FROM di_edw.al_lang l
WHERE l.NAME = al_lang.NAME AND l.object_type = 0 AND l.TYPE = 0)
ORDER BY 1, 2
regards
Prem[/code]
biexplorer (BOB member since 2007-06-21)
Modified the query a little bit and joined to the job status table to also report on last run time and next run time.
select sched_name, name job_name,
case when recurrence_pattern = -2147483521 then 'DAILY' else to_char(recurrence_type) end recurrence,
case when recurrence_pattern = -2147483521 then 0
when recurrence_pattern > 0 then round(log(2,recurrence_pattern)+1)
else round(log(2,power(2,31) + recurrence_pattern)+1) end sched_day,
case when recurrence_pattern = -2147483521 then 'ALL'
when recurrence_pattern > 0 and recurrence_type = 'WEEKLY'
then decode(round(log(2,recurrence_pattern)+1) ,1,'MONDAY',2,'TUESDAY',3,'WEDNESDAY',4,'THURSDAY',5,'FRIDAY',6,'SATURDAY',7,'SUNDAY')
when recurrence_pattern < 0 and recurrence_type = 'WEEKLY'
then decode(round(log(2,power(2,31) + recurrence_pattern)+1),1,'MONDAY',2,'TUESDAY',3,'WEDNESDAY',4,'THURSDAY',5,'FRIDAY',6,'SATURDAY',7,'SUNDAY' )
when recurrence_pattern > 0
then to_char(round(log(2,recurrence_pattern)+1))
else
to_char(round(log(2,power(2,31) + recurrence_pattern)+1)) end sched_date,
to_char(al_sched_info.start_time, 'HH24:MI') sched_time, alvw_history.start_time job_start_time,
to_char(trunc(sysdate)+(alvw_history.execution_time/60/60/24),'HH24:MI:SS') execution_time
from al_sched_info, al_lang, alvw_history
where al_sched_info.job_guid = al_lang.guid
and al_lang.version = (select max(version) from al_lang where al_lang.guid = al_sched_info.job_guid)
and al_lang.name = alvw_history.service (+)
and nvl(alvw_history.start_time,sysdate) = nvl((select max(start_time) from alvw_history where service = al_lang.name),sysdate)
and al_sched_info.active = 1
parse_di_schedule.txt (1.0 KB)
butanski 
(BOB member since 2009-05-08)