Display all jobs in the JDE Scheduler system in an efficient & easy-to-read format.
Like most of the applications in JDE that were built for system administrators, the JDE Scheduler Application P91300 is pretty awful to use. Critical information is scattered throughout several different screens without one good screen to overview the entire schedule.
This query has been optimized to be information-rich in a manner that is readable and easy to share with others. It depends on SQL Server to provide automatic timezone conversion, but can be modified for Oracle or DB2.
- Search & replace the appropriate database schema prefixes in the FROM section:
- JDE910.SY910 – Your JDE System Library that contains F91300 (JDE Scheduler)
- JDE_PRODUCTION.PRODCTL – Your Production Control Data (UDC Descriptions)
- JDE_PD910.PD910 – Your Production Central Objects (Batch Version Descriptions)
- JDE910.OL910 – Your Object Librarian (Batch Program Description)
- Execute using any SQL tool to receive full list of JDE Scheduler.
- Review the results & share with others.
It is possible to embed this query into an Excel spreadsheet that contains automatic data refresh. We share a copy of the spreadsheet throughout our organization, so that everyone has read-only access to the JDE Scheduler. It contains formatting that helps clearly identify recurrence types and adds nice sort & filter functionality. Please leave a note if you are interested in learning more about how to implement this solution.
SELECT CASE SJSCHJBSTAT WHEN '01' THEN 'Active' ELSE 'Inactive' END as [Active?], SJSCHRCRTYP as [Recurrence Code], CASE SJSCHRCRTYP WHEN '61' THEN 'Repeat Every '+CAST(SJSCHNUMMNS as VARCHAR(2))+' Minutes' WHEN '62' THEN 'Repeat Every '+CAST(SJSCHNUMHRS as VARCHAR(2))+' Hours' WHEN '21' THEN 'Specific Day(s):' + CASE WHEN SJMONDAY = 1 THEN 'M' ELSE '' END + CASE WHEN SJTUESDAY = 1 THEN 'T' ELSE '' END + CASE WHEN SJWEDNESDAY = 1 THEN 'W' ELSE '' END + + CASE WHEN SJTHURSDAY = 1 THEN 'Th' ELSE '' END + CASE WHEN SJFRIDAY = 1 THEN 'F' ELSE '' END + CASE WHEN SJSATURDAY = 1 THEN 'Sa' ELSE '' END + CASE WHEN SJSUNDAY = 1 THEN 'Su' ELSE '' END WHEN '11' THEN 'Daily (Every Day)' WHEN '12' THEN 'Once Every Weekday' ELSE Rec.DRDL01 END as [Recurrence Desc], CAST(dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as TIME(0)) as [Launch Time], -- Auto-adjust for local timezone current offset SJSCHRPTNM as [Batch Program], SIMD as [Batch Program Desc], SJSCHVER as [Batch Version], VRJD as [Batch Version Desc], SJSCHJBNM as [Scheduled Job Name], SJSCHJBTYP as [Job Type], dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as [Start Date], SJCURRUNCNT as [Run Count], SJSCHENHV as [Environment], SJSCHJOBSVR as [Server], SJJOBQUE as [Job Queue], DATEADD(DAY, SJUPMJ % 1000 - 1, DATEADD(YEAR, SJUPMJ/ 1000, 0)) as [Last Updated] FROM [JDE910].[SY910].[F91300] LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 Rec ON SJSCHRCRTYP = RTRIM(LTRIM(Rec.DRKY)) AND Rec.DRSY = 'H91' AND Rec.DRRT = 'RC' LEFT JOIN JDE_PD910.PD910.F983051 Versions ON VRPID = SJSCHRPTNM AND VRVERS = SJSCHVER LEFT JOIN JDE910.OL910.F9860 OL ON SJSCHRPTNM = SIOBNM AND SIFUNO = 'UBE' ORDER BY SJSCHRCRTYP DESC, -- Reccurrence Type SJSCHNUMHRS ASC, -- 'Repeat Every x Hours' SJSCHNUMMNS ASC, -- 'Repeat Every x Minutes' CAST(dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as TIME(0)) ASC -- Launch Time (Daily)