How to cancel indexing jobs with a Pending status in SAS® Visual Investigator on SAS® Viya® 3.5 and the SAS® Viya® platform


In some scenarios, SAS Visual Investigator indexing jobs might remain in a Pending status indefinitely. This issue can also cause all new indexing jobs to remain in a Pending status, which affects normal system operation.

This issue occurs in both SAS Viya 3.5 and SAS Viya platform environments.

SAS Viya 3.5

Data indexing job with a Pending Status

SAS Viya Platform

Data indexing job with a Pending status

To cancel the Pending indexing jobs, ensure there are no scheduled jobs to run, or stop the indexer service to ensure that no other indexing jobs get in the way. Start the indexer service after the job is cancelled so that new indexing jobs can come in.

Workaround

SAS Viya 3.5

1. Identify blocking jobs.

Run the following query on the Internal Postgres DB (Shared Services):

SELECT job_timings.job_execution_id, create_time, start_time, end_time, string_val
FROM (SELECT job_execution_id, create_time, start_time, end_time
      FROM svi_indexer.dh_batch_job_execution
      WHERE job_execution_id IN (
          SELECT job_execution_id
          FROM svi_indexer.dh_batch_job_execution_params
          WHERE string_val IN (
              SELECT string_val
              FROM svi_indexer.dh_child_job_execution_queue AS blocks
                       INNER JOIN
                  (SELECT * FROM svi_indexer.dh_batch_job_execution_params WHERE key_name = 'jobId') AS jobs
              ON blocks.job_execution_id = jobs.job_execution_id))) AS job_timings
         INNER JOIN (
    SELECT job_execution_id, string_val
    FROM svi_indexer.dh_batch_job_execution_params
    WHERE key_name = 'objectTypeName' AND
            job_execution_id IN (
            SELECT job_execution_id
            FROM svi_indexer.dh_batch_job_execution_params
            WHERE string_val IN (
                SELECT string_val
                FROM svi_indexer.dh_child_job_execution_queue AS blocks
                         INNER JOIN
                    (SELECT * FROM svi_indexer.dh_batch_job_execution_params WHERE key_name = 'jobId') AS jobs
                ON blocks.job_execution_id = jobs.job_execution_id))) AS job_titles ON job_timings.job_execution_id = job_titles.job_execution_id
ORDER BY start_time;

Note: If the query above returns any results, delete all rows from the svi_indexer.dh_child_job_execution_queue table. Otherwise, the rows will become blocks to future indexing jobs.

2. Clear blocking jobs.

delete from svi_indexer.dh_child_job_execution_queue;

3. Check indexing jobs and tasks.

SAS Visual Investigator Indexing Jobs

select job_id, * from feature.job where status_cd IN ('Running', 'Pending') order by started_at_dttm desc;

SAS Visual Investigator Indexing Tasks

select parent_id, name_nm,  status_cd, message_txt , started_at_dttm, ended_at_dttm, * from  feature.task where status_cd IN ('Running', 'Pending') order by status_cd;

4. Update the job and task status.

UPDATE feature.task SET status_cd = 'Cancelled' WHERE parent_id IN (SELECT job_id FROM feature.job WHERE status_cd = 'Pending' AND name_nm LIKE '%Data indexing%');

      UPDATE feature.job SET status_cd = 'Cancelled' WHERE status_cd = 'Pending' AND name_nm LIKE'%Data indexing%';


After completing these steps, verify on the SAS Visual Investigator Admin Jobs tab that the indexing jobs are displayed with a Canceled status. After confirming the cancellation, proceed with running a new indexing job.