Stuck in Progress (tasks / Events) & Garbage Collection
Tasks can become stuck in progress for a variety of reasons, but the symptom is that view submitted tasks shows the task / event as stuck in progress or the associated workflow job is left in an unapproved state. You may wish to track these anomalies in your deployment, if so please read on...
[font=Comic Sans]Task Persistence Database[size=7][size][font]
TP Database structure, which is leveraged by View submitted tasks feature within the IME, involves 5 differet DB tables:
1. event_object12_5
2. event12_5
3. lock12_5
4. object12_5
5. tasksession12_5
The 2 tables that report the state of the task/event are:
event12_5 and tasksession12_5. On both tables there is a field called state which will indicate at the task/event state.
Here is a list of the possible states:
All the task states are mapped to 'pending' and 'in progress' except for 'completed', 'cancelled' and 'rejected'.
Please reference the above table that's associated with this post, but under all circumstances you should attempt to leverage the OOTB garbage collection, which you can invoke via the IME and it will clean up "Completed", "Rejected", "Invalid", and "Aborted" tasks.
To get a bit more technical, here's a snippet from the code:
(tasksession12_5.state = 512) -- mark for deletion OR (tasksession12_5.state = 128) -- completed OR (tasksession12_5.state = 256) -- cancelled OR (tasksession12_5.state = 32) -- rejected OR (tasksession12_5.state = 2) -- invalid OR (tasksession12_5.state = 1048576) -- task aborted
Again, these state codes can be cross-referenced and extrapolated from the above diagram
I.e., "In Progress" or "Pending" = TP states 0, 1, 2, 4, 8, 16, 64.
'Failed' tasks do not have a unique state because the tasks can fail during different events within the task. Failed tasks will have the last logged state at the time of the failure which could be one of the pending or in process states.
BEGIN_STATE = 0x00; // 0
PRE_STATE = 0x01; // 1
INVALID_STATE = 0x02; // 2
PENDING_STATE = 0x04; // 4
EXECUTING_STATE = 0x08; // 8
APPROVED_STATE = 0x10; // 16
REJECTED_STATE = 0x20; // 32
POST_STATE = 0x40; // 64
COMPLETED_STATE = 0x80; // 128
CANCELLED_STATE = 0x100; // 256
MARK_FOR_DELETION_STATE = 0x200; // 512
UNKNOWN_STATE = 0x300; // 768
INITIAL_STATE = 0x400; // 1024
PRIMARY_PENDING_STATE = 0x800; // 2048
PRIMARY_COMPLETE_STATE = 0x1000; // 4096
SECONDARY_PENDING_STATE = 0x2000; // 8192
AUDIT_STATE = 0x4000; // 16384
So state 2 is failed as you can see INVALID_STATE.
It's important to keep your task persistence database lean and in peak performance, by regularly implementing OOTB garbage collection procedures here:
If you've neglected to run garbage collection on your submitted tasks within the task persistence database, it's possible that the OOTB garbage cleanup procedure can easily become overwhelmed.. If your garbage collection is taking exceedingly long to complete, you may want to consider implementing this, but would strongly encourage you to only leverage this feild developed utility on your development / test environment, as it's NOT fit for production use.
[font=Comic Sans]Workpoint Database[size=7][size][font]
Within Workpoint tables you can determine if a Job is in progress by executing the following query:
[color=#f60505]
select proci_id, proci_db from wp_proci where proc_state_id = 7
[color]
The above query will retrieve the IDs of all created jobs. When you want to find ‘Pending’ workflow enabled jobs that are stuck in progress, then this is the query you need.
A date range where INITIATION_DATE > {ts '2012-01-03 12:00:00'} and INITIATION_DATE <{ts '2012-01-04 00:00:00'} This will need to be changed to reflect the current date range when you run this SQL statement.
Additionally, attached to this thread is the garbage collection procedure for workpoint specifically.
Workpoint support has indicated that selecting only the parent jobs isn't necessary because the Workpoint delete stored procs only deletes the jobs where the parent is marked to be deleted. For instance if you update a sub-proc and not the parent, the sub-proc will not be deleted.
Please respond with any questions or concerns.
Thank you.
Regards,
Chris Thomas
CA Technologies
Principal Support Engineer
Identity Manager Reporting Expert
Tel: +1-631-342-4360
Chris.Thomas@ca.com