Resource unit is not released after the job has completed

Document ID : KB000095560
Last Modified Date : 16/07/2018
Show Technical Document Details
Issue:
One or more jobs are stuck in RESWAIT status, waiting for a resource. When running the LISTRESOURCESTATUS NAME("name") command in the CLI perspective, you notice the resource(s) in question show(s) units in use by jobs which are in fact already in COMPLETE status. Resources have not been released causing other jobs to stay in RESWAIT status.
Environment:
CA Workload Automation 11.3 SP3
Cause:
On the DE Manager, the resource thread has been unable to update the CAWADB in order to release the resource(s) after the job(s) completed.. This could be caused by DB connection issues or anything that hampers the resource thread connection with the DB. 
Resolution:
This issue is fixed in r12.0 SP2 build 1042 with testfix T5EO312, where the resource thread has been enhanced to recover from such scenarios.

In DE 11.3 SP3, the following SQL must be run in order to identify the held resource and release it manually.

Identify the order_id value of the held resource:
select * from ESP_RM_ORDER JOIN ESP_RM_ORDER_ITEM ON ESP_RM_ORDER.ORDER_ID = ESP_RM_ORDER_ITEM.ORDER_ID WHERE SATISFIED_TIME IS NOT NULL AND RESOURCE_NAME='<res_name>' ORDER BY REQUEST_TIME

Replace <res_name> with the actual resource name.

Identify the order_id value of any rows that have an old request_time value corresponding to the date/time the job(s) in question completed.
This query will also list resources being used correctly and legitimately at the time by running jobs, so be careful not to touch those.

Once you have determined the order_ids for the rows which are satisfied and have not been released but should have been, you can issue the delete statements as below: 

DELETE FROM ESP_RM_ORDER WHERE ORDER_ID=<order_id> 
DELETE FROM ESP_RM_ORDER_ITEM WHERE ORDER_ID=<order_id> 

If you are unsure of which order_ids to delete, please contact CA Support for assistance.

After this, you can run the LISTRESOURCESTATUS NAME("name") command in the CLI perspective to check if the resource units have been released.
Additional Information:
NOTE: A Cold Start of the DE will also clear the resources.