Failed Query message in event log - Job stuck in Running - CAUAJM_E_18402 ORA-01422: exact fetch returns more than requested number of rows

Products

• Autosys Workload Automation

Issue/Introduction

We are seeing Failed query message in event logs.  One of the jobs got impacted and we are unable to see the job definition but we can see the job status.

Please Check and suggest how to correct the issue. Thanks.

JOB Name: test-job-name

1/19/2021 09:48:16]      <Executing at DEV_AGENT>

[11/19/2021 09:48:16]      CAUAJM_E_18416 Event Server: <AUTOSYS_DEV_PDB>  Failed Query: <BEGIN :RetVal := ujo_post_event (:I_eoid, :I_joid, :I_job_ver, :I_status, :I_box_joid, :I_evt_num, :I_event_time_gmt, :I_run_num, :I_ntry, :I_exit_code, :I_pid, :I_jc_pid, :I_machine, :I_wf_joid, :I_over_num, :I_esp_lstatus, :I_esp_status, :B_count, :B_ret_val, :B_box_joid, :B_box_term, :B_status, :B_oldstat); END; <<'DEV0d1aeqp00',463309,1,5,0,1222792285,1637333286,245454725,1,-657,'','','testserver.company.com',1,-1,'','Job is not RUNNING. chase sending a FAILURE event.'>>>

[11/19/2021 09:48:16]      CAUAJM_E_18402 ORA-01422: exact fetch returns more than requested number of rows

[11/19/2021 09:48:16]      ORA-06512: at "AEDBADMIN.UJO_POST_EVENT", line 68

 

Environment

Release : 12.0

Component : CA Workload Automation AE (AutoSys)

Resolution

You should not have more than one row for each job where is_active=1 and is_currver=1 in the ujo_job table.

The message suggests you have more.

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

Try running:
update ujo_job set is_active=0 where joid=<joid_for_job_with_problem>;

update ujo_job set is_currver=0 where joid=<joid_for_job_with_problem>;

commit;

Then reinsert the job via jil.