autorep -j jobname -q gives "CAUAJM_E_18402 ORA-00904: "B"."SEQ

Products

Autosys Workload Automation

Issue/Introduction

Following error message is produced when running command "autorep -J <jobname> -q".

/* ----------------- jobname1 ----------------- */

insert_job: jobname1 job_type: BOX
owner: autosys
permission:
date_conditions: 0
description: "jobname1"
alarm_if_fail: 1
group: GRP1

/*CAUAJM_E_18416 Event Server: <SERVER1> Failed Query: <SELECT a.as_group, a.job_name, a.job_type, a.service_desk, a.send_notification, a.mach_name, a.n_retrys, a.has_resource, a.owner, a.permission, a.as_applic, a.job_name, a.box_terminator, a.has_condition, a.description, b.run_window, b.term_run_time, b.run_calendar, b.timezone, b.start_mins, b.priority, b.start_times, b.alarm_if_fail, b.auto_delete, b.auto_hold, b.must_start, b.must_complete, b.min_run_alarm, b.max_run_alarm, b.date_conditions, b.job_load, b.exclude_calendar, b.days_of_week, c.ulimit, c.std_out_file, c.std_in_file, c.std_err_file, c.shell, c.interactive, c.heartbeat_interval, c.elevated, c.command, c.chk_files, d.job_class, d.job_terminator, d.max_exit_success, d.profile, e.fail_codes, e.envvars, e.success_codes, f.as_group, f.job_name, f.job_type, f.service_desk, f.send_notification, f.mach_name, f.n_retrys, f.has_resource, f.owner, f.permission, f.as_applic, f.job_name, f.box_terminator, f.has_condition, f.description, g.run_window, g.term_run_time, g.run_calendar, g.timezone, g.start_mins, g.priority, g.start_times, g.alarm_if_fail, g.auto_delete, g.auto_hold, g.must_start, g.must_complete, g.min_run_alarm, g.max_run_alarm, g.date_conditions, g.job_load, g.exclude_calendar, g.days_of_week, h.ulimit, h.std_out_file, h.std_in_file, h.std_err_file, h.shell, h.interactive, h.heartbeat_interval, h.elevated, h.command, h.chk_files, i.job_class, i.job_terminator, i.max_exit_success, i.profile, j.fail_codes, j.envvars, j.success_codes FROM ujo_job a LEFT OUTER JOIN ujo_sched_info b ON a.joid = b.joid AND a.job_ver = b.job_ver AND a.over_num = b.over_num AND a.seq_num = b.seq_num LEFT OUTER JOIN ujo_command_job c ON a.joid = c.joid AND a.job_ver = c.job_ver AND a.over_num = c.over_num AND a.seq_num = c.seq_num LEFT OUTER JOIN ujo_job d ON a.joid = d.joid AND a.job_ver = d.job_ver AND a.over_num = d.over_num AND a.seq_num = d.seq_num LEFT OUTER JOIN ujo_sched_info e ON a.joid = e.joid AND a.job_ver = e.job_ver AND a.over_num = e.over_num AND a.seq_num = e.seq_num LEFT OUTER JOIN ujo_job f ON a.joid = f.joid AND a.job_ver = f.job_ver AND a.over_num = f.over_num AND a.seq_num = f.seq_num LEFT OUTER JOIN ujo_sched_info g ON a.joid = g.joid AND a.job_ver = g.job_ver AND a.over_num = g.over_num AND a.seq_num = g.seq_num LEFT OUTER JOIN ujo_command_job h ON a.joid = h.joid AND a.job_ver = h.job_ver AND a.over_num = h.over_num AND a.seq_num = h.seq_num LEFT OUTER JOIN ujo_job i ON a.joid = i.joid AND a.job_ver = i.job_ver AND a.over_num = i.over_num AND a.seq_num = i.seq_num LEFT OUTER JOIN ujo_sched_info j ON a.joid = j.joid AND a.job_ver = j.job_ver AND a.over_num = j.over_num AND a.seq_num = j.seq_num WHERE i.joid = 4290 AND i.is_active = 1 AND i.is_currver = 1 ORDER BY a.seq_num ASC NULLS FIRST >

CAUAJM_E_18402 ORA-00904: "B"."SEQ_NUM": invalid identifier

CAUAJM_I_18403 Processing OCI function not used(4)

CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.

CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <864>

*/

Environment

Release : 11.3.6

Component : CA Workload Automation AE (AutoSys)

Resolution

Problem is that job information is corrupted. In order to correct the problem we had to set the job to inactive in the database. Then re-insert the job definition.

1. Query to get the joid and validate the active status

select joid, is_active from aedbadmin.ujo_job where job_name = 'jobname1';

2. set the job to inactive using joid from above sql statement

update aedbadmin.ujo_job set IS_ACTIVE = 0 where joid = nnnnn;

It is recommended to delete and re-insert the job afterwards so that it gets new joid.

Additional Information

The outcome of following SQL statement will produces a list of of jobs that would have the problem:

select joid, job_name, count(*) from ujo_job WHERE is_active = 1 and is_currver = 1 group by joid, job_name having count(*) > 1;

If no rows are given, then the table is fine.
If it returns a jobname, then you would need to run following SQL statement

select joid, job_name, job_ver, is_active, is_currver from ujo_job where job_name='<job_name>' order by job_ver desc;