When running an 'autorep -J ALL -q > jil_file.txt' to export and back up all job definitions, the export is interrupted by the following Oracle errors:
Release : 11.3.6
Component : CA Workload Automation AE (AutoSys)
This error occurs when there are jobs/boxes that have more than one row in the ujo_job table with the following values:
is_currver=1
is_active=1
There should only ever be one row with the above column values per job. This row corresponds to the latest saved version of the job definition (active job definition).
Previous versions of the job definition are stored with '0' values for the aforementioned columns (inactive job definition).
1) Identify the jobs that have more than one active row defined with a query like the following:
SELECT job_name, joid, is_active, is_currver, COUNT(*)
FROM ujo_job
where is_active=1 and is_currver=1
GROUP BY job_name, joid, is_active, is_currver
HAVING COUNT(*) > 1
Example result of one job with 2 'active' rows:
JOB_NAME JOID IS_ACTIVE IS_CURRVER COUNT(*)
---------------------------------------------------------------- ---------- ---------- ---------- ----------
testjob_0036 100 1 1 2
Job 'testjob_0036' has 2 "active" rows when it should only have 1.
2) Identify the jobs and also which row is the latest version:
SELECT a.job_name, a.job_ver, a.joid, a.create_stamp, a.update_stamp, a.is_active, a.is_currver
FROM ujo_job a
JOIN (SELECT job_name, is_active, is_currver, COUNT(*)
FROM ujo_job
where is_active=1 and is_currver=1
GROUP BY job_name, is_active, is_currver
HAVING count(*) > 1 ) b
ON a.job_name = b.job_name
AND a.is_active = b.is_active
AND a.is_currver = b.is_currver
ORDER BY a.job_name, a.job_ver
Example result:
JOB_NAME JOB_VER JOID CREATE_ST UPDATE_ST IS_ACTIVE IS_CURRVER
---------------------------------------------------------------- ---------- ---------- --------- --------- ---------- ----------
testjob_0036 17 100 12-JUL-19 27-APR-20 1 1
testjob_0036 18 100 12-JUL-19 27-APR-20 1 1
The crucial thing here is the JOB_VER column. The one with the highest value contains the latest version of the job. That row is the one that should be active, having is_active=1 and is_currver=1.
In the above example, there is a version 17 and a version 18 of the job. The row with JOB_VER=18 should be the only one with is_active=1 and is_currver=1. The one with the older job version, JOB_VER=17, should have values of '0' in those columns.
3) Update the table to correct this
In the above example, this sql correctly sets the row with version 17 to 'inactive':
update ujo_job set is_currver=0, is_active=0 where joid=100 and job_ver=17;
Do the above for all job versions that were identified in the above steps
NOTE: It is highly recommended to stop the Scheduler and back up the AEDB before performing manual updates to the tables. This can allow you to revert the changes by restoring the AEDB.