SAS Viya Platform

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, parameter_value
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 parameter_value IN (
            SELECT parameter_value
            FROM svi_indexer.dh_child_job_execution_queue AS blocks
            INNER JOIN
            (SELECT * FROM svi_indexer.dh_batch_job_execution_params WHERE parameter_name = 'jobId') AS jobs
            ON blocks.job_execution_id = jobs.job_execution_id
        )
    )
) AS job_timings
INNER JOIN (
    SELECT job_execution_id, parameter_value
    FROM svi_indexer.dh_batch_job_execution_params
    WHERE parameter_name = 'objectTypeName'
      AND job_execution_id IN (
        SELECT job_execution_id
        FROM svi_indexer.dh_batch_job_execution_params
        WHERE parameter_value IN (
            SELECT parameter_value
            FROM svi_indexer.dh_child_job_execution_queue AS blocks
            INNER JOIN
            (SELECT * FROM svi_indexer.dh_batch_job_execution_params WHERE parameter_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.

Note: In the SAS Viya platform, the SAS Visual Investigator Admin Jobs tab enables you to cancel jobs via the Cancel the Job button. Sometimes, though, jobs can remain in the Canceling status. The back-end status shows Stopping. As a result, you might need to manually perform the cancellation using the above queries. 

SAS Visual Investigator Indexing Jobs Table

SELECT job_id, * 
FROM svi_visual_investigator.svi_job 
WHERE status_cd IN ('Running', 'Pending', 'Stopping') 
ORDER BY started_at_dttm DESC;

SAS Visual Investigator Indexing Tasks Table

SELECT parent_id, name_nm, status_cd, message_txt, started_at_dttm, ended_at_dttm, *
FROM svi_visual_investigator.svi_task 
WHERE status_cd IN ('Running', 'Pending', 'Stopping') 
ORDER BY status_cd;  

4. Update the job and task status.

• Update the indexing jobs to canceled

UPDATE svi_visual_investigator.svi_job SET status_cd = 'Cancelled' WHERE status_cd IN ('Pending', 'Stopping');

• Update indexing tasks to Canceled

UPDATE svi_visual_investigator.svi_task SET status_cd = 'Cancelled'  WHERE status_cd IN ('Pending', 'Stopping');