Basic SQL querries

Find JOB_ID for a given job.

select job_name, joid
from MDBADMIN.UJO_JOBROW
where job_name ='';

Jobs with ALARM_IF_FAIL set to 0

select JOB_NAME from MDBADMIN.UJO_JOBROW where ALARM_IF_FAIL = 0;

Filewatchers with no term_run_time

select JOB_NAME from MDBADMIN.UJO_JOBROW  
where JOB_TYPE like 'f' and TERM_RUN_TIME = 0;

Jobs that run overnight and have an exclude calendar.

select job_name from MDBADMIN.UJO_JOBROW
where start_times like '23*' and exclude_calendar is not null;

Discinct Commands for a client.

set linesize 240 PAGESIZE 5000 TAB OFF
select distinct(COMMand) from MDBADMIN.UJO_JOBROW where JOB_NAME like 'IOPRINC%'
and MACHINE = 'sims-asp4'
and job_type like 'c'
order by COMMand;

Jobs with null group

set linesize 160 PAGESIZE 5000
select job_name, AS_GROUP from MDBADMIN.UJO_JOBROW where AS_GROUP IS NULL;

calck – Display jobs with specific calendars (Supports Wildcards).

set linesize 160 PAGESIZE 5000
select JOB_NAME, RUN_CALENDAR, EXCLUDE_CALENDAR from MDBADMIN.UJO_JOB
where (RUN_CALENDAR like 'UK_HOL_HNN_N' or EXCLUDE_CALENDAR like 'UK_HOL_HNN_N') and JOB_NAME like '%';

Display Calendars having a particular date.

set linesize 160 PAGESIZE 5000
select NAME from MDBADMIN.UJO_CALENDAR where DAY like **'2014-05-26'**;

Jobs using specific timezone

set linesize 160 PAGESIZE 5000
select job_name, TIMEZONE from MDBADMIN.UJO_JOBROW where TIMEZONE='GB-Eire';

Jobs with Status

Job statuses: RU=1; FA=5; SU=4; TE=6; OI=7; IN=8; AC=9; OH=11 ST=3

set linesize 160 PAGESIZE 5000 COLSEP ' '
select job_name, status from MDBADMIN.UJO_JOBROW where status like '3';

Currently Running or Starting jobs.

set linesize 160 PAGESIZE 5000 COLSEP ' '
select job_name from MDBADMIN.UJO_JOBROW where status = 3 or status = 1;

Currently Failed or Terminated jobs.

set linesize 160 PAGESIZE 5000 COLSEP ' '
select job_name from MDBADMIN.UJO_JOBROW where job_name like 'XAOPPUA%' and (status = 5 or status = 6;

Jobs with specific conditions

You may optionally pipe the output in to a while loop and run an autorep command and grep out inset and condition keywords. autorep -q -J “$line” -l0 | egrep “insert|condition:”

set linesize 170 PAGESIZE 50000
select MDBADMIN.UJO_JOB.job_name, lookback from MDBADMIN.UJO_JOB_COND 
left join MDBADMIN.UJO_JOB on MDBADMIN.UJO_JOB.joid=MDBADMIN.UJO_JOB_COND.joid
where MDBADMIN.UJO_JOB.JOB_name = 'jobname'

Current Overrides.

set linesize 170 PAGESIZE 5000
select MDBADMIN.UJO_OVERJOB.over_num, MDBADMIN.UJO_job.job_name, MDBADMIN.UJO_OVERJOB.entity 
from MDBADMIN.UJO_OVERJOB 
left join MDBADMIN.UJO_job ON MDBADMIN.UJO_job.joid=MDBADMIN.UJO_OVERJOB.joid;

Who created this job and when

set linesize 160 PAGESIZE 5000
select MDBADMIN.UJO_JOB.job_name, MDBADMIN.UJO_JOB2.CREATE_USER, MDBADMIN.UJO_JOB2.CREATE_STAMP
from MDBADMIN.UJO_JOB 
FULL JOIN  MDBADMIN.UJO_JOB2 ON  MDBADMIN.UJO_JOB.JOID=MDBADMIN.UJO_JOB2.JOID
where  MDBADMIN.UJO_JOB.job_name like 'jobname%'
order BY MDBADMIN.UJO_JOB2.CREATE_USER;

Find duplicate commands in AutoSys.

set linesize 160 PAGESIZE 5000
select job_name from MDBADMIN.UJO_JOBROW 
where command like (select command from MDBADMIN.UJO_JOBROW
where job_name like 'DBINHOU%' group by command having count (command) > 1);

Jobs on a machine

set linesize 160 PAGESIZE 5000 COLSEP ' '
select job_name from MDBADMIN.UJO_JOBROW
where MACHINE='simsun3';

Jobs with profile

set linesize 160 PAGESIZE 5000 COLSEP ' '
select job_name, PROFILE from MDBADMIN.UJO_JOBROW where PROFILE='/export/home/rydex000/autosys.profile';

Jobs with command

set linesize 240 PAGESIZE 5000 TAB OFF
select job_name from MDBADMIN.UJO_JOBROW where COMMand like '%SWEEP%';

Total number of jobs

set linesize 160 PAGESIZE 5000
select COUNT(job_name) from MDBADMIN.UJO_JOBROW;

No of jobs by group

set linesize 160 PAGESIZE 5000 COLSEP ' '
select AS_GROUP, COUNT(AS_GROUP) from MDBADMIN.UJO_JOBROW
GROUP BY AS_GROUP order by COUNT(AS_GROUP) DESC;

Find Avg. run-time for Jobs where we have data for atleast 3 runs

select MDBADMIN.UJO_JOB.job_name, MDBADMIN.UJO_AVG_JOB_RUNS.AVG_RUNTIME
from MDBADMIN.UJO_AVG_JOB_RUNS
left join MDBADMIN.UJO_JOB on MDBADMIN.UJO_AVG_JOB_RUNS.joid=MDBADMIN.UJO_JOB.joid
where job_name like 'DBINHOUXXSA_reorg_aspsun4_15-xamin_opp' 
and MDBADMIN.UJO_AVG_JOB_RUNS.NUM_RUNS > 2 
and job_type like'c';

Find jobs running longer than their MAX_RUN_ALARM

Solution for EST (GMT-5) Timezone. Please replace + (5*3600) with your timezone for correct epochtime.

select MDBADMIN.UJO_JOB.job_name,(((select ((SYSDATE - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) * 24 * 60 * 60) + (5*3600) from DUAL)-STARTIME)-(MDBADMIN.UJO_JOB.max_run_alarm*60))/60 time_beyond_MRA
from MDBADMIN.UJO_JOB_RUNS
left join MDBADMIN.UJO_JOB on MDBADMIN.UJO_JOB_RUNS.joid=MDBADMIN.UJO_JOB.joid
where MDBADMIN.UJO_JOB_RUNS.STATUS='1' and MDBADMIN.UJO_JOB.max_run_alarm not like 0 
and (((select ((SYSDATE - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) * 24 * 60 * 60) + (5*3600) from DUAL)-STARTIME)-(MDBADMIN.UJO_JOB.MAX_RUN_ALARM*60))/60 > 0;

Find non existent jobs in job conditions.

select APPTABLE.job_name, COND  from MDBADMIN.UJO_JOB
right join (
  select JOB_NAME, COND_JOB_NAME COND from MDBADMIN.UJO_JOB_COND
  full join MDBADMIN.UJO_JOB on MDBADMIN.UJO_JOB.joid=MDBADMIN.UJO_JOB_COND.joid
  where MDBADMIN.UJO_JOB.job_name like 'IOBNY%' and OVER_NUM like '-1'
  and MDBADMIN.UJO_JOB_COND.COND_JOB_NAME IS NOT NULL and TEST_GLOVALUE IS NULL
) APPTABLE
on MDBADMIN.UJO_JOB.job_name=APPTABLE.COND
where MDBADMIN.UJO_JOB.joid is null;

Generic sql queries that may be of use in AutoSys.

Convert Sysdate to epoch time.

Taken from http://www.epochconverter.com

select (select (SYSDATE - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) * 24 * 60 * 60 from DUAL)

Convert Sysdate to epoch time.

set linesize 160 PAGESIZE 5000
select TABLE_NAME, COLUMN_NAME from all_tab_columns  where COLUMN_NAME like '%COLUMN_NAME%';