I wish to generate a report displaying the next start times for job which date and time conditions.
In the table ujo_job_status there is a field named next_start, but I do not know how to decipher it.
We see values like '145086300'.
How do I translate that into a human friendly date and time?
With this information I believe I could then find jobs with a start time older than today.
Workload Automation AE
Database - Any
Platform - Any
The view ujo_jobst would be ideal for querying the "next_start" times in the database.
It contains columns like job_name, last_start, last_end, status, next_start, run_machine
from multiple tables such as ujo_job_status, ujo_job, ujo_extended_run_info.
Many of the date time fields in the AE database tables are stored in UNIX epoch time. (# of seconds since 1970).
SQL can be used to convert them into a more human friendly for format or one may make use of the AE utility "time0".
The command "time0" is an autosys command that can convert / display times.
CAUAJM_I_50096 Current AutoTime(internal): 1460307914
The above displays the current time/date in epoch form
$ time0 -a 1460307914
CAUAJM_I_50097 External Time: 04/10/2016 12:05:14
The above converts the epoch time passed to it with the -a option to a human friendly format.
$ time0 -t "01/01/2016 13:00"
CAUAJM_I_50099 Autotime(internal): 1451674800
The above converts January 1st 2016 at 13:00 to epoch time.
Oracle Example :
TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((next_start -18000) /(60*60*24)),'YYYYMMDDHH24MISS')
order by job_name;
SQL Server Example - how to convert epoch times
dateadd(second,convert(int,(next_start -18000)),'Jan 1 1970' )
order by job_name
Sybase Example - how to convert epoch times
convert(char(28),dateadd(second,convert(int,(next_start -18000)),'Jan 1 1970' ),109)
order by job_name
The above examples are displaying the job names and what the next_start values looks like in epoch time and then a more human
friendly version of it would look like for jobs that have date conditions, sorted by the job names.
NOTE - (next_start - 18000) in the SQL examples accounts for a GMT offset. This will vary based on your timezone and DST status. To get the current gmt_offset for your server, you can run "select int_val from alamode where type='gmt_offset'". This gmt_offset query could also be embedded in the examples in place of the 18000 to ensure the current offset is used.
The forecast command and also show predicted start times for jobs as well.
The autorep detail report can display the next / future STARTJOB for a job which has date/time conditions.
Please refer to the man pages or reference guide for more details on the above mentioned commands.