How to generate a list of next start times for jobs with date and time conditions.
Article ID: 40911
Updated On:
Products
Issue/Introduction
Problem:
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.
Environment:
Workload Automation AE
Database - Any
Platform - Any
Resolution:
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.
Example:
$ time0
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 :
select substr(job_name,1,20),next_start,
TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((next_start -18000) /(60*60*24)),'YYYYMMDDHH24MISS')
from aedbadmin.ujo_jobst
where date_conditions=1
order by job_name;
SQL Server Example - how to convert epoch times
select job_name,next_start,
dateadd(second,convert(int,(next_start -18000)),'Jan 1 1970' )
from ujo_jobst
where date_conditions=1
order by job_name
Sybase Example - how to convert epoch times
select
job_name,
next_start,
convert(char(28),dateadd(second,convert(int,(next_start -18000)),'Jan 1 1970' ),109)
from ujo_jobst
where date_conditions=1
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.
Additional information:
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.
Environment
Component:
Feedback
