How to generate a list of next start times for jobs with date and time conditions.

book

Article ID: 40911

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) CA Workload Automation Agent

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

Release: ATSYHA99000-11.3.6-Workload Automation AE-High Availability Option
Component: