JIl export is interrupted by ORA-00904 invalid identifier error

Products

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

Issue/Introduction

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:

Environment

Release : 11.3.6

Component : CA Workload Automation AE (AutoSys)

Cause

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).

Resolution

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

Additional Information

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.