antoine_mpo
Reputable Poster
Hi list,
Several times i wanted to extract a list of scheduled job in JDE, especially to look at schedule times, in my time zone, but so far, i couldn't find a way to get this information by sql, because i couldn't spend too much time to do so.
Lately i really needed this information, and finally took the time to find a solution with Oracle SQL.
I want to share it with you. Maybe it could help some of you.
The information are located in F91300.
You can easily find the job name, its status, the ube name and version, the job queue.
Then there is the field SJSCHSTTIME where is stored what is called "Scheduled Start Date/time" in P91300 where you define jobs.
The Data dictionary says that this alias is "based on Universal Time Coordinate (UTC) Time. UTC Time is the number of seconds elapsed since Midnight on January 1, 1970 in Greenwich, England. UTC Time used to be called Greenwich Mean (GM) Time."
The thing is that data stored here is not the number of seconds since 1970-01-01, but the number of minutes (thanks to JDEList, because it's in a post here that i learned that).
So first, we will convert this SJSCHSTTIME to a standard date/time field.
To do so, we will use the oracle function NUMTODSINTERVAL(n,interval_unit), which allow to add n intervals to a date/time, specifying the interval unit that can be 'DAY','HOUR','MINUTE','SECOND'.
It goes like this : TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')
What you get here is the start date/time, in GMT hours. If it's what you need, you're done, easy as pie.
If you want it in your time zone, and that Daylight saving apply to your zone, there is a bit of extra work, a littre trickier.
It requires several type conversions, and the use of FROM_TZ function.
First you need to find your zone name. Use the oracle view 'v$timezone_names', with the following sql :
select distinct tzname from v$timezone_names ;
Find yours. Mine was 'Europe/Paris', for instance.
The function FROM_TZ convert a timestamp type to a timestamp_tz type which include time zone. So first of all, you need to convert your GMT date/time to a timespamp with a
'CAST(gmt_date) AS TIMESTAMP'
Then you can use FROM_TZ to convert a UTC time to your zone :
FROM_TZ(gmt_timestamp,'UTC') AT TIME ZONE 'YourZoneName'
So, if you put it all together, the full formula is (with my time zone) :
FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP),'UTC') AT TIME ZONE 'Europe/Paris'
Of course now you can change the way to display this date/time, using the usual TO_CHAR and format that suit you. For instance if you only want to display
time in 24h format :
TO_CHAR(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Paris','HH24:MI:SS')
So here is an example of a sql to get a list of your scheduled jobs, in Europe/Paris time :
select trim(a.SJSCHJBNM) Job_Name,trim(a.SJSCHRPTNM) UBE_Name, trim(a.SJSCHVER) UBE_Version,
a.SJSCHSTTIME ScheduleStart_JDE, trim(SJSCHJBSTAT) Status, trim(a.SJSCHUSER) "User", trim(a.SJJOBQUE) Job_Queue,
to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','DD/MM/YYYY HH24:MI:SS') ScheduleStart_DateTime,to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','HH24:MI:SS') ScheduleStartTime_Local
from sys7333.f91300 a
where a.SJSCHJBSTAT ='01'
order by 9 ;
Hope it helps.
Bye.
Several times i wanted to extract a list of scheduled job in JDE, especially to look at schedule times, in my time zone, but so far, i couldn't find a way to get this information by sql, because i couldn't spend too much time to do so.
Lately i really needed this information, and finally took the time to find a solution with Oracle SQL.
I want to share it with you. Maybe it could help some of you.
The information are located in F91300.
You can easily find the job name, its status, the ube name and version, the job queue.
Then there is the field SJSCHSTTIME where is stored what is called "Scheduled Start Date/time" in P91300 where you define jobs.
The Data dictionary says that this alias is "based on Universal Time Coordinate (UTC) Time. UTC Time is the number of seconds elapsed since Midnight on January 1, 1970 in Greenwich, England. UTC Time used to be called Greenwich Mean (GM) Time."
The thing is that data stored here is not the number of seconds since 1970-01-01, but the number of minutes (thanks to JDEList, because it's in a post here that i learned that).
So first, we will convert this SJSCHSTTIME to a standard date/time field.
To do so, we will use the oracle function NUMTODSINTERVAL(n,interval_unit), which allow to add n intervals to a date/time, specifying the interval unit that can be 'DAY','HOUR','MINUTE','SECOND'.
It goes like this : TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')
What you get here is the start date/time, in GMT hours. If it's what you need, you're done, easy as pie.
If you want it in your time zone, and that Daylight saving apply to your zone, there is a bit of extra work, a littre trickier.
It requires several type conversions, and the use of FROM_TZ function.
First you need to find your zone name. Use the oracle view 'v$timezone_names', with the following sql :
select distinct tzname from v$timezone_names ;
Find yours. Mine was 'Europe/Paris', for instance.
The function FROM_TZ convert a timestamp type to a timestamp_tz type which include time zone. So first of all, you need to convert your GMT date/time to a timespamp with a
'CAST(gmt_date) AS TIMESTAMP'
Then you can use FROM_TZ to convert a UTC time to your zone :
FROM_TZ(gmt_timestamp,'UTC') AT TIME ZONE 'YourZoneName'
So, if you put it all together, the full formula is (with my time zone) :
FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP),'UTC') AT TIME ZONE 'Europe/Paris'
Of course now you can change the way to display this date/time, using the usual TO_CHAR and format that suit you. For instance if you only want to display
time in 24h format :
TO_CHAR(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Paris','HH24:MI:SS')
So here is an example of a sql to get a list of your scheduled jobs, in Europe/Paris time :
select trim(a.SJSCHJBNM) Job_Name,trim(a.SJSCHRPTNM) UBE_Name, trim(a.SJSCHVER) UBE_Version,
a.SJSCHSTTIME ScheduleStart_JDE, trim(SJSCHJBSTAT) Status, trim(a.SJSCHUSER) "User", trim(a.SJJOBQUE) Job_Queue,
to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','DD/MM/YYYY HH24:MI:SS') ScheduleStart_DateTime,to_char(FROM_TZ(CAST((TO_DATE('1970-01-01', 'YYYY-MM-DD') + numtodsinterval(SJSCHSTTIME, 'MINUTE')) AS TIMESTAMP), 'UTC')
AT TIME ZONE 'Europe/Paris','HH24:MI:SS') ScheduleStartTime_Local
from sys7333.f91300 a
where a.SJSCHJBSTAT ='01'
order by 9 ;
Hope it helps.
Bye.