Need to retrieve the archived cases from the DB using Search functionality
I am looking for some guidance regarding search of archived cases from the archive DB . Once the archiving exercise is completed, then the cases will not be no longer available in the Transactional DB. But, if user wants to open the case which is in archive db, then what changes are required in the pega application which can retrieve the case information from archive DB ?
Any thoughts !
***Updated by moderator: Lochan to update Categories***
At one customer site they have setup an archive solution with a way to search and restore a case.
They have another database schema (can be also in another database server) named archive with the same tables structure for live data. They have created a stored procedure to daily move (insert and then delete) cases with all related rows (links, attachments, history, party indexes, ...) from prpc database to archive database.
In the sysadmin database table rule, they define the archive database as the alternate database for report. So, for the search archive, they are able to search using report on the archive database using the alternate link. The report search just gives a limited set of columns. If the user wants to see the details, he need to restore it. So when he click on the review button, it run another stored procedure which move back the individual case from archive to prpc database. Once done, they are able to review the case from the prpc database.
The restored case will automatically be archived again through the daily archiving stored procedure which is based on the resolution timestamp.
They have also setup a permanent delete stored procedure to definitively remove very old cases from the archive database.
I have another query regarding archive search. How to deal with Elastic search items ?. In the case manager portal, if user searches for the cases which are in archive DB, then how can we retrieve the information and showcase to the user ?
I hope you have viewed my second comment for this post as well.
If you use a stored procedure (or even direct queries to the database) to delete the entries from the main table because the data has been moved to the archive, Pega platform doesn't realise this and thus Elastic Search would not know (so the index will contain the old data unless a full re-index is triggered from the search landing page for that class or set of classes).
As of now, that is the only option because we cannot track changes to tables which are not going through our Obj-Save / Obj-Delete methods for the index to be updated.
With Elastic Search in 7.1.7 onward, the queue table and agent activity have changed calling different APIs now, so in that sense any custom changes that you do will need to be modified again upon upgrade if you are in lower version.
Indexing external content is still an open area for the product along with how to search and index archived content.
You could potentially write triggers on these table which upon delete could populate the pr_sys_queue_ftsindexer table with the pzInsKeys of the records deleted. But this will cause duplicate entries during normal deletes (obj-delete).