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:

  1. Server map tablespaces for the respective server (E.g. svm910)
  2. Start time and End time of the period
  3. 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');


In the above SQL, 1/12 is the correction factor we are adding to convert UTC time to CET time. It actually represents 2 (hours)/24 which was reduced to 1/12. So add/substract your correction factor accordingly.
This should give you the desired output.
Hope this tutorial was helpful. Please use the comment box below for any queries.

Leave a Reply

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