Issue with Cross instance dependent jobs

Products

CA Workload Automation AE - Scheduler (AutoSys)

Issue/Introduction

Even though the job doesn't have cross instance dependency in job definition, sql query is providing the output as the x_instance is present.
in spite of including version check in 2nd query the discrepancy is existing

Below is the eg:
DG2>
insert_job: ibb4.edwtuc_p_c_CUST_HQ_LOC_MAP_OUTB_LOAD job_type: CMD
command: $FW_HOME/etl_wrapper.ksh CORE_REPL_LOAD_CUST_HQ_LOC_MAPPING EDWSD
machine: GLOB.VM_ms_edwsem_ops
owner: etlp
permission: gx
date_conditions: 0
condition: v(GLOB.ops_p1) = "up" & v(GLOB.edwedsp) = "up" & s(ibb4.edwtuc_p_c_CUST_HQ_LOC_MAP_OUTB,0)
description: "This is the job that runs replication load for CORE_REPL_LOAD_GDV_ADJUSTMENTS"
std_out_file: "$$ibb4_etlp_log_dir/$AUTO_JOB_NAME-$AUTORUN.out"
std_err_file: "$$ibb4_etlp_log_dir/$AUTO_JOB_NAME-$AUTORUN.err"
max_run_alarm: 120
alarm_if_fail: 1
profile: "/ngs/app/etlp/fwscripts1/ETLFRAMEWORK/PROFILES/.profileedsp"
job_load: 1
priority: 1
alarm_if_terminated: 1
group: edwtuc-s2f
application: ibb4
svcdesk_pri: 2
svcdesk_imp: 2
svcdesk_sev: 2

SQL> SELECT COND_JOB_AUTOSERV FROM (
SELECT j.job_ver, c.COND_JOB_AUTOSERV,
c.COND_JOB_NAME, RANK() OVER (ORDER BY j.JOB_VER DESC) r
FROM UJO_JOB_COND c,
UJO_JOB j
WHERE j.JOB_NAME = 'ibb4.edwtuc_p_c_CUST_HQ_LOC_MAP_OUTB_LOAD'
and j.JOID = c.JOID
and j.job_ver = c.job_ver
)
where r=1
and COND_JOB_AUTOSERV is not null; 2 3 4 5 6 7 8 9 10 11
COND_JOB_AUTOSERV
----------------------------------------------------------------
DG1

SQL> SELECT COND_JOB_AUTOSERV FROM (
SELECT j.job_ver, c.COND_JOB_AUTOSERV,
c.COND_JOB_NAME, RANK() OVER (ORDER BY j.JOB_VER DESC) r
FROM UJO_JOB_COND c,
UJO_JOB j
WHERE j.JOB_NAME ='ibb4.edwtuc_p_c_CUST_HQ_LOC_MAP_OUTB_LOAD'
and j.JOID = c.JOID
and j.job_ver = c.job_ver
And j.IS_CURRVER ='1'
)
where r=1
and COND_JOB_AUTOSERV is not null
; 2 3 4 5 6 7 8 9 10 11 12 13
COND_JOB_AUTOSERV
----------------------------------------------------------------
DG1

Can you please check and help on this?

why the query is not providing the correct output

Environment

Release : 11.3.6

Component : CA Workload Automation AE (AutoSys)

Resolution

To be clear, this is not a product problem or error.
The autorep and job_depends commands are returning accurate results.

The problem was with the client's custom query.
The db tables and schema are for the
products use and will change from version to version.
Custom queries can and will break at times as the product evolves.
Use the commands provided with the product to get your reporting data
or open enhancement requests on the communities website
if there is some report needed that the product or SDK/APIs
does not offer you a way to get.

The client's query issue was, it appeared to not
be taking into account that there can be multiple
versions of the job in the ujo_job and ujo_job_cond table.

This would be more accurate:

SELECT COND_JOB_AUTOSERV FROM (
SELECT j.job_ver, c.COND_JOB_AUTOSERV,
c.COND_JOB_NAME, RANK() OVER (ORDER BY j.JOB_VER DESC) r
FROM UJO_JOB_COND c,
UJO_JOB j
WHERE j.JOB_NAME = 'waae.cross_instance_check'
and j.JOID = c.JOID
and j.job_ver = c.job_ver
and j.is_active=1)
where r=1
and COND_JOB_AUTOSERV is not null;

Again, in the future we encourage you to use autorep or job_depends
if you are wanting to see what a job has for its dependencies.