How to find UBEs running on a server at a particular time in JD Edwards
Recently I was asked to provide data about the UBEs, running on one of our batch servers, during a particular time interval. Following is the approach that can be used to obtain the relevant information.
Prerequisites
Before proceeding with extracting the data we would require following details ready with us:
- Server map tablespaces for the respective server (E.g. svm910)
- Start time and End time of the period
- Time difference between the UTC and Timezone used by JDE
Process
Server map tables, F986110 – Job Master and F986114 – Job Audit, will be used to perform a join and filter the data.
For our e.g. let us say that we wish to find the jobs that were active during 10th Aug 2015 at 18:20:02 CET on Enterprise Server ENTPRD01. The tablespace used by server map for this enterprise server is, for e.g, svmprd01910. The time difference between UTC and CET is 2 hours. JDE stores the submit date and time in F986110 as CET time, while it stores the job start time and end time in F986114 as UTC time. You can find out the difference from JDE thin client. Go to WSJ and select any job record which got submitted and processed immediately without going to waiting status, note down the job submit time. Now go to Row exit > Execution details, Audit data tab. Note down the Start date and time mentioned. Calculate the difference between the two timings to obtain this correction factor.
SQL
Following is the SQL to be used:
SELECT to_char(jobaudit.jcstdtim+1/12, 'hh24:mi:ss'), to_char(jobaudit.jcetdtim+1/12, 'hh24:mi:ss'), jobmaster.*, jobaudit.*, (jobaudit.jcetdtim - jobaudit.JCSTDTIM) * 60 * 60 * 24 as ExecutionTime
FROM svmprd01.F986110 jobmaster, svmprd01.F986114 jobaudit
WHERE jobmaster.jcjobnbr = jobaudit.jcjobnbr
AND (jobaudit.jcstdtim+1/12) <= to_date('2015-08-10 18:20:02','yyyy-mm-dd hh24:mi:ss')
AND (jobaudit.jcetdtim+1/12) >= to_date('2015-08-10 18:20:02','yyyy-mm-dd hh24:mi:ss');
