Question
Verizon
IN
Last activity: 18 May 2017 4:07 EDT
Hard Deleting Work Object Data using SQL
Hi,
Our client wishes to implement a clean up process by using SQL queries to be run as and when required.
So, we have identified the following tables from which thw work object and its dependent data to be deleted.
PC_DATA_WORKATTACH
PC_LINK_ATTACHMENT
PC_ASSIGN_WORKLIST/PC_ASSIGN_WORKBASKET
WORK OBJECT TABLE
Can you please let me know if hard deleting the data from these tables would be enough or we have to consider any other tables ?
Also, what additional processing should we consider while performing this operation.?
Thanks.
***Updated by moderator: Lochan to update Categories***
Pegasystems Inc.
AU
Hi,
Enclosed are the list of tables;
delete from pc_data_uniqueid
update pc_data_uniqueid set pylastreservedid=0 //To reset the work object ID numbering sequence
delete from <Application Work pool associated database table name>
delete from pc_work
delete from pc_assign_worklist
delete from pc_assign_workbasket
delete from pc_history_work
delete from pc_data_workattach
delete from pc_index_workparty
delete from pc_link_attachment
delete from pc_link_folder
It is always recommended to take the back up of the tables and We could either use the following methods to perform work object clean up task;
RDB-Save and RDB-Delete
Obj-Delete
Pegasystems Inc.
IN
Have you considered using purge and archive wizard ?
Pegasystems Inc.
IN
As Shantanu suggested, it would be better to use OOTB Purge and Archive wizard.
However Purge and Archive wizard only works on resolved work objects. Could you let us know your exact requirement so that same can be taken as feedback?
Verizon
IN
Our requirement is to either "hard delete" or "resolve and delete" active work objects. For resolved cases I can use purge and archive OOTB. But for active cases I think I will have to manually remove the entries from all the work processing related tables.
PEG
GB
This isn't something that I've done before, so cannot offer any advice based on experience.
But a couple of points
1. Do you use the search gadget (Lucene/Elastic Search)?
If so I should imaging the search index will still contain entries for any work you delete.
2. Do you use SLAs?
Again, if so you're likely to have entries in the PR_SYS_QUEUE_SLA table which won't be able to find the parent work object.
I'm not sure if this will be handled silently or reported as an error in the logs, so I would suggest that you test for this.
Lastly, returning to the Purge and Archive. If you're intended to remove active work, would you want to consider calling the UpdateStatus activity to set the states to Resolved-<your choice>, then using the Purge feature?