sas-run-tool: Service executor failed to execute successfully: exit status 1

[gwootton]

When a file gets upload to the files service, the data is physically stored in a large object in postgres.

This includes files uploaded through SAS Content, but also for any job that has run, the results of that job.

This job history, including logs, is preserved based on the expiration attribute of the job, which is set based on the "expiresAfter" parameter of the jobRequest.

When you create a jobRequest through SAS Studio, this expiration is set to 72 hours. When you create a job in Environment Manager, the expiration is not set, meaning the job history is preserved indefinitely.

I created a series of pyviyatools tools to address the issue of job history being preserved indefinitely:
1. setjobrequestexpire.py - https://github.com/sassoftware/pyviyatools/blob/master/setjobrequestexpire.py - Allows you to set the expiresAfter parameter for jobRequests. This will cause any future jobs run under that jobRequest to carry an expiration based on that parameter.
2. deletejobhistory.py - https://github.com/sassoftware/pyviyatools/blob/master/deletejobhistory.py - Allows you to delete the job history (contents of the Monitoring tab) based on filters, so for example any jobs that completed over a week ago, or all the history for a specific job.
3. deleteorphanedfiles.py - https://github.com/sassoftware/pyviyatools/blob/master/deleteorphanedfiles.py - When you delete a job history object, the associated files for that object should be deleted as well automatically. Sometimes this process fails resulting in a file object without a valid parent (as the job history object has been deleted). This tool identifies any files objects without a parent (i.e. the parentUri when called returns a 404), and deletes them.

So #1 prevents future job history from being preserved indefinitely and #2 and #3 clean up existing history.

For exports, the transfer service has functions to create an export package and download it. After you've downloaded it, the package is still present on the server and could be downloaded again, until such time as you delete the export package. When I'm doing an export, I include a delete to keep the database free of old packages once they've been downloaded:
sas-viya transfer export (produces ID as output)
sas-viya transfer download --id id-from-above
sas-viya transfer delete --id-from-above

You can also remove old packages using the sas-viya transfer delete CLI command if you no longer need them on the server, the sas-viya transfer list command will show you the packages that still exist on the server.

[paterd2]

Mr.Gwooton, 

I had some problems with the quoting 😉

This works for the Update CAS Audit Data, we have a lot of records, I also could have used createdBy,sas.audit. 

python3 deletejobhistory.py -e -f 'eq(jobRequest.name,"Update CAS Audit data")' -p 1000 -l 100 -w

Thank you, we will add 72 hours or so,  to our jobs that don't have an expiry set.

I will also take a look at the orphan script.

Regards

Dik

[paterd2]

Mr. Gwooton

I still have some job related entries for example : 

select count(*) from workloadorchestrator.job_std_output ;

  count

---------

 4495370

(1 row)

SharedServices=# select count(*) from workloadorchestrator.job_std_output where line_txt like '%App.ComputeServer.Communication.HTTP%';

  count

---------

 4266126

(1 row)

SharedServices=# SELECT pg_size_pretty(pg_total_relation_size('workloadorchestrator.job_std_output')) AS table_size;

 table_size

------------

 3118 MB

(1 row)

•  Do you know how we can also get rid of these entries ?

I cannot find a logger with the name App.ComputeServer.Communication.HTTP

sas-viya -p $ns workload-orchestrator logs levels get | grep Loggers |awk -F"name:" '{for (i = 1; i <= NF; i++) {print $i}}'| grep -v NULL
Loggers [map[level:INFO
App.NLS] map[level:ERROR
App.tk.HTTPC.Conn] map[level:FATAL
App.tk.HTTPC.State] map[level:FATAL
App.tk.HTTPC.debug] map[level:FATAL
App.tk.jwtkeystore] map[level:INFO
App.tk.HTTPC.DNSCache] map[level:FATAL
App.tk.eam.rsa.Buffer] map[level:FATAL
App.tk.eam.ssl.openssl111] map[level:ERROR
App.Grid.SGMG.Log.Config.JSON] map[level:FATAL
App.SQLServices.RTE.Statement] map[level:FATAL
App.SQLServices.RTE.Environment] map[level:FATAL
App.SQLServices.RTE.Connection.DCM] map[level:FATAL
App.SQLServices.RTE.Statement.POSTGRES.stats]]

[gwootton]

These records are not for jobs from the jobExecution service (those that show in the Monitoring tab of the Jobs and Flows page in SAS Environment Manager) but records stored by SAS Workload Orchestrator related to its job history. This would include anything launched to SWO, so would include all compute server, connect server and batch servers that result in a pod being created. The table you are looking at, job_std_output, captures a row for each line returned to stdout from each job up to the maxStdOutLines configuration in SWO.

Removing these entries would remove the job history from the Workload Orchestrator page in Environment Manager. There is no function within SWO to remove these entries, so if you wanted to remove old entries from this table you would need to manipulate the tables in the database directly rather than through any interface Viya provides.

If you engage SAS Technical Support, they can provide you with a script you can run on a SAS Workload Orchestrator StatefulSet pod to remove old entries.

The script accepts as arguments a retention period in days and limit in the total number of job records to process, and it builds and executes a SQL query to:

1. Build a temporary table of job IDs that ended prior to the retention period up to the limit provided from the main job table.
2. For each of those jobs, delete any associated records in the supporting tables.
3. For each of those jobs, delete the record in the main job table.
4. VACUUM ANALYZE and REINDEX each table.

Because this can take a long time to finish, the script nohup's the SQL query so you don't need to remain connected to the pod while it runs.

[paterd2]

Thank you, with this thread and the information you gave me , we are more in control.