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

insert_job: job1234 job_type: CMD
command: test1234.sh
machine: VM123
owner: autosys
permission: gx
date_conditions: 0
condition: v(var1) = "up" & v(var2) = "up" & s(job3456,0)
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: "/profiles/.profile"
job_load: 1
priority: 1
alarm_if_terminated: 1

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 = 'job1234'
and j.JOID = c.JOID
and j.job_ver = c.job_ver
)
where r=1
and COND_JOB_AUTOSERV is not null;

COND_JOB_AUTOSERV
----------------------------------------------------------------
AB1

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 ='job1234'
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;

COND_JOB_AUTOSERV
----------------------------------------------------------------
AB1

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.