Question
Cigna Corporation
US
Last activity: 18 Oct 2022 1:53 EDT
Purging maintenance tables
We have a pega 8.5.6 installation at Cigna and the two largest tables in the database are :
PR_PERF_STATS - 117 million rows
PR_DATA_SVCINVHIST - 105 million rows
Is it ok to truncate these tables ?
***Edited by Moderator Marije to add Capability tags***
Pegasystems Inc.
GB
Cigna Corporation
US
@MarijeSchillern Thanks for the info on the perf stats table - is there a similar method available for the other table ?
Updated: 13 Oct 2022 4:08 EDT
LTIMindtree
SA
Hi Timo,
Based on the Tables provided, you're using Pega Robotics.
Instead of Purging DB Tables manually, It's advisable to create / setup Archival and Purging DB Tables where as Records will be moved to Archival Table first and Purging the Records later. DBA's can help you to achive this requirement by creating Stored Procedures as a Job in the Database side.
You can define the Archival of Records from DB Tables older than 30days and move it to Purging Table post 60days. You can adjust them as per your Organization / DBA's advise.
Cigna Corporation
US
@Kishore Sanagapalli I am the DBA. That's why I placed the ticket. we do not want to archive this data because nobody needs it. So can I just truncate to cleanup the DB ?
I believe the answer is yes ,,,,,,
Tim
Pegasystems Inc.
GB
@TimothyK8255 please see the answer provided in Purge and Archival strategy.
Cigna Corporation
US
@MarijeSchillern That did not seem to answer my question.....I did read it
Pegasystems Inc.
GB
@TimothyK8255 yes it's a rather complex topic :-)
I found the following:
"We suggested 3 options as mentioned below t
1). You can wait until Pega 8.7 for starting case archival.
For pr_perf_stats table with large data, truncation may be performed if required. Other work tables and related ones will likely not be large and not growing rapidly."
I can ask our SME from that post to comment directly on this post if you prefer?
LTIMindtree
SA
Thanks for your update. Yes you can truncate those old records with a Query as you know it better than me. However, Have it a discussion with Application Team and ddecide that How many days old Records that you wanted to retain and proceed. It's a better option.
@TimothyK8255 you logged your question under Pega Robotic Automation tag. Can you confirm this is for Pega Platform only?
There are some questions on the forum where this topic was raised before: Do we need to consider PR_PERF_STATS for Reorg Job at DB side?
Also see Node and cluster default job schedulers
Did you check the available documentation under Trimming the pr_perf_stats table - does this answer your question?