[SQL] JDE Scheduler Super Query

Purpose

Display all jobs in the JDE Scheduler system in an efficient & easy-to-read format.

About

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.

A sample output of the JDE Scheduler "Super Query"
Preview of query output
Usage
  1. Search & replace the appropriate database schema prefixes in the FROM section:
    1. JDE910.SY910 – Your JDE System Library that contains F91300 (JDE Scheduler)
    2. JDE_PRODUCTION.PRODCTL – Your Production Control Data (UDC Descriptions)
    3. JDE_PD910.PD910 – Your Production Central Objects (Batch Version Descriptions)
    4. JDE910.OL910 – Your Object Librarian (Batch Program Description)
  2. Execute using any SQL tool to receive full list of JDE Scheduler.
  3. Review the results & share with others.
Excel Template

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.

JDE Scheduler in Excel - Preview
JDE Scheduler Query loaded into Excel
Query
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)

Comments 5

  • Hi – what is pchem.fromJDE?

    • Hi John, sorry for the (very) long delay in getting back to you. You’re the very first human being to post a relevant comment on my blog, so thanks!

      To answer your question: PChem.FromJDE() is a user-defined SQL scalar function that converts a JDE Julian Date number into a real SQL Date field. We created it in a custom “PChem” schema to avoid touching official DTA & CTL schemas and use it all over in SQL reporting queries. We also have an equally-useful PChem.ToJDE() that will convert a SQL Date (or properly formatted string) into a JDE Julian Number.

      Here are their definitions. You may want to adjust the schema name (and be sure that it actually exists in your database!)

      USE [JDE_PRODUCTION]
      GO

      /****** Object: UserDefinedFunction [PChem].[FromJDE] Script Date: 6/5/2018 2:43:20 PM ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE FUNCTION [PChem].[FromJDE](@julian numeric(18,0)) RETURNS DATE
      AS
      BEGIN
      DECLARE @returnDate date;

      SELECT @returnDate = DATEADD(DAY, @julian % 1000 - 1, DATEADD(YEAR, @julian/ 1000, 0));

      RETURN @returnDate;
      END;

      GO

      CREATE FUNCTION [PChem].[ToJDE](@date DATE) RETURNS numeric(18,0)
      AS
      BEGIN
      DECLARE @returnJulian numeric(18,0);

      SELECT @returnJulian = DATEDIFF(Year,'1900-01-01',@date) * 1000 + DATEPART(DayOfYear, @date);

      RETURN @returnJulian;
      END;

      GO

      Alternatively, you can simply “inline” the function definition into the SQL query, replacing PChem.FromJDE(SJUPMJ) with an equivalent SQL expression. For example:


      SELECT
      -- ... other fields ...,
      DATEADD(DAY, SJUPMJ % 1000 - 1, DATEADD(YEAR, SJUPMJ/ 1000, 0))
      FROM [JDE910].[SY910].[F91300]

      UPDATE: I modified the query in the blog post, removing the reference to PChem.FromJDE() and replacing it with an inline equivalent.

  • Hi Brandon, do you have the SQL in Oracle database format. I tried converting it but there are too many errors that I get

  • Thank you for this!!!!

    Changed a bit, most output is in Portuguese, as I’m Brazilian:

    SELECT
    SJSCHJBNM as Scheduled_Job_Name,
    SJSCHRPTNM as Batch_Program,
    SJSCHVER NOME_VERSAO,
    SJJOBQUE FILA_EXEC,
    SJSCHRCRTYP as Recurrence_Code,
    CASE WHEN to_char(SJSCHRCRTYP) = ’61’ THEN ‘Repete cada ‘ || SJSCHNUMMNS || ‘ Minuto(s)’
    WHEN to_char(SJSCHRCRTYP) = ’62’ THEN ‘Repete cada ‘|| SJSCHNUMHRS || ‘ Hora(s)’
    when to_char(SJSCHRCRTYP) = ’31’ THEN ‘Repete todo dia ‘|| SJSCHNUMDY || ‘ do mes’
    when to_char(SJSCHRCRTYP) = ’32’ THEN case when to_char(SJWEEKDAY) = ‘1’ then ‘Repete todo último dia útil do mes’
    else ‘Repete todo último dia do mes’ end
    WHEN to_char(SJSCHRCRTYP) = ’21’ THEN ‘Dia(s) especifico(s): ‘ || CASE WHEN to_char(SJMONDAY) = ‘1’ THEN ‘Seg ‘ ELSE ” END
    || CASE WHEN to_char(SJTUESDAY) = ‘1’ THEN ‘Ter ‘ ELSE ” END
    || CASE WHEN to_char(SJWEDNESDAY) = ‘1’ THEN ‘Qua ‘ ELSE ” END
    || CASE WHEN to_char(SJTHURSDAY) = ‘1’ THEN ‘Qui ‘ ELSE ” END
    || CASE WHEN to_char(SJFRIDAY) = ‘1’ THEN ‘Sex ‘ ELSE ” END
    || CASE WHEN to_char(SJSATURDAY) = ‘1’ THEN ‘Sab ‘ ELSE ” END
    || CASE WHEN to_char(SJSUNDAY) = ‘1’ THEN ‘Dom ‘ ELSE ” END
    WHEN to_char(SJSCHRCRTYP) = ’11’ THEN ‘Todo Dia’
    WHEN to_char(SJSCHRCRTYP) = ’12’ THEN ‘Uma vez por dia útil’
    ELSE to_char(Rec.DRDL01)
    END as Recurrence ,
    TO_DATE(‘1970-01-01 00:00’, ‘YYYY-MM-DD HH24:MI’) + (SJSCHSTTIME / 60 / 24) Start_date,
    (SELECT substr(to_char(FROM_TZ(CAST(TO_DATE(‘1970-01-01 00:00’, ‘YYYY-MM-DD HH24:MI’) + (SJSCHSTTIME / 60 / 24) AS TIMESTAMP), ‘Greenwich’) AT TIME ZONE ‘-3:00’), 9,9) FROM DUAL) as Start_Hora,
    SJCURRUNCNT as Run_Count,
    SJSCHENHV as Environment,
    SJSCHJOBSVR as Server,
    substr(to_char(to_date(SJUPMJ+1900000, ‘YYYYDDD’)), 0,8) as Last_Updated
    FROM
    SY900.F91300
    LEFT JOIN PRODCTL.F0005D Rec ON SJSCHRCRTYP = RTRIM(LTRIM(Rec.DRKY)) AND Rec.DRSY = ‘H91’ AND Rec.DRRT = ‘RC’
    LEFT JOIN PD900.F983051 Versions ON VRPID = SJSCHRPTNM AND VRVERS = SJSCHVER
    LEFT JOIN OL900.F9860 OL ON SJSCHRPTNM = SIOBNM AND SIFUNO = ‘UBE’
    where to_char(SJSCHJBSTAT) = ’01’
    and SJSCHENHV like ‘%PD%’;

    • Hi Vanessa,
      Thanks for your contribution. I appreciate the effort you put in to modify the date/time conversions for another DB.

      Can you clarify if this query was re-written for Oracle or for DB2?

      Thanks,
      Brandon

Leave a Reply to brandon Cancel reply

Your email address will not be published. Required fields are marked *