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

You are seeing a failed query message in the event logs.  One of the jobs got impacted and you are unable to see the job definition but you can see the job status.

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; <<'DEV######',463309,1,5,0,1222792285,1637333286,245454725,1,-657,'','','testserver.example.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

2. 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;

3. Then reinsert the job via jil.