What transpires in the eHealth software and its Oracle Database when an element is deleted?
- The elements entry and any associated child elements are removed from the element configuration and group member tables (nh_element,nh_elem_assoc, nh_group_members)
- An entry for each element deleted is added to the nh_deleted_element table
- The Statistics rollup job will delete the element entries from the nh_deleted_element table after the entry has existed for 7 days. This is known as a 'soft delete' in the relational database world.
* Note: If in a clustered environment, the front end does not utilize the nh_deleted_element table, elements get immediately deleted on the front end. It is on the Back End (BE) Pollers where the 'soft delete' occurs as described above.
Statistics data associated to the deleted elements is not purged by the Statistics rollup job. The data remains in the database until it is rolled out normally (age of data exceeds maximum rollup setting, e.g. 52 weeks of daily data by default).
In early versions of eHealth the statistics rollup job would delete the data associated with the deleted elements. This was causing severe performance issues with the eHealth servers Statistics Rollup job. It was decided that the limited space savings accrued by this activity was not worth the performance expense of immediately removing it.
CA Professional Services offers a package (nhDeleteSplitData) to delete stats data for deleted elements. It is only available from the CA Professional Services team. Please contact CA's Professional Services group for more information on this offering.
If deleted element recovery is required, please contact CA's Professional Services group for more information on offerings that may provide what is sought. Alternatively the only viable self implemented solution is to load a previous database save which contains the elements that were just deleted and in need of recovery.
Common follow up questions that might arise after reviewing the above information:
Q1 – If the deleted element is kept in the nh_deleted_element DB table for 7 days, why can’t it be recovered? Why not ‘hard delete’ it right away if its not recoverable?
A1 - It is done this way to correctly handle element deletion for clustered environments. While the deleted element is removed immediately from the Reporting Front End (RFE) console system, it remains in the nh__deleted_element table of the BE Poller that managed the element for 7 days before removal.
Q2 – If we keep the deleted elements data in the DB until it ages out via the Statistics Rollup schedule, why can’t we show the data in reports the way we can for retired elements? After all the data remains in the DB at that point.
A2 - Retired elements are still present in the nh_element and nh_elem_assoc tables, allowing for reporting requests to find and gather the necessary historic data for the retired element. When an element is deleted the references to its existence have been removed as part of the deletion process, so this is not possible.
Said a bit differently:
The primary reason a deleted element is kept in the nh_deleted_element table for 7 days is for synchronization tasks in clustered eHealth environments. This allows time for the various synchronization tasks in a cluster to run and keep things synchronized between systems.
The primary reason recovery of deleted elements is a very difficult and time consuming process is the elements removal from associations and group membership table data. Once broken it is no trivial task to recover it.
The difference between deleted and retired is that all these changes are only made to deleted elements. Retired elements are effectively shut down from polling without further changes in the DB. That results in the retained ability to report on and view historical data. But even then once that data ages out of the DB on the regular roll up schedules, its as good as deleted.