Query to get jobs without down/upstream dependencies and not in the box

Products

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

Issue/Introduction

In order to move PROD jobs from R11 to R11.3.6,  we need to find out all stand alone jobs (non upstream/downstream dependencies) and non in the  box. Could you please help with the query that does that ?

Environment

Release:
Component: ATSYS

Resolution

You would need two different queries for that :

Query #1: 

select job_name from ujo_job where is_active=1 and is_currver=1 and box_joid=0 and job_type!=98 and joid not in (select joid from ujo_job_cond where type in ('s','f','d','t','n','e')); 

The above query will give you a list of jobs that... 
Excludes: 
- Jobs that are a box 
- Jobs that are in a box 
- Jobs that have any job dependencies based on another job's status (success, failure, done, terminated, not running) 
- Jobs that have dependencies based on other job's exit code 
- Jobs that have external dependencies on jobs from other instances 

Includes: 
- Jobs that have Global Variable dependencies 
- Jobs that have successor jobs (see limitations below) 

Limitations: 
- The list DOES include jobs that have successor jobs. 
So if you have: 
JOB1 - CMD job, not in a box, no job dependencies (no predecessors) 
JOB2 - condition: s(JOB1) 

JOB1 will be included in the query result list. The query is unable to exclude jobs that have other jobs with dependencies on them (successor jobs). 

To help identify these jobs with successors, here's another query: 

Query #2: 

SELECT ujo_job.job_name, ujo_job_cond.COND_JOB_NAME, ujo_job_cond.TYPE FROM ujo_job_cond INNER JOIN ujo_job ON ujo_job_cond.joid = ujo_job.joid and ujo_job_cond.job_ver = ujo_job.job_ver WHERE ujo_job.is_currver=1 and ujo_job.is_active=1 and ujo_job.box_joid=0 and ujo_job.job_type!=98 and ujo_job_cond.type IN ('s','f','d','t','n','e'); 

The above query will give you a list of jobs (job_name) along with the name of the predecessor job they depend on (cond_job_name) and the type of dependency. 
s = success 
f = failure 
d = done 
t = terminated 
n = not running 
e = exit code 

Excludes: 
- Jobs that are a box 
- Jobs that are in a box 
- Jobs that have Global Variable dependencies 

You can cross reference the result of Query #1 with the result of Query #2. 
If any 'job_name' values from Query #1 are in the 'job_cond_name' column in Query #2, then youwill know that job has a successor. 

For this example: 
JOB1 - CMD job, not in a box, no job dependencies (no predecessors) 
JOB2 - condition: s(JOB1) 

The above query would give: 
job_name job_cond_name type 
JOB2 JOB1 s