CPR Orphans
Under certain conditions a given Call Processing Record (CPR) may have all Call Routing Number (CRN) entries with which it is associated deleted through the TFNRegistry > RouteLink®.
Because the CPR entries can be quite large, it is important to minimize the number of these "orphaned" records.
Periodic maintenance on the table that stores the CPR data prevents accumulation of these entries and ensures database parameters such as size and performance are not negatively impacted.
The cleanup of the orphaned CPRs can be facilitated by using a script that identifies and removes any CPR entries that no longer have any associated CRN entries. It is intended that this script be run on a regular basis ideally using an automated mechanism such as cron (recommended to run once a week).
Below is a sample SQL script that facilitates the cleanup operation:
-- log the number of orphan records found before cleaning them up
SELECT 'Total ', COUNT(*), 'orphaned CallProcessingRecord entries.'
FROM CallProcessingRecord
WHERE sha1 NOT IN (SELECT DISTINCT(cpr_sha1) FROM CallRoutingNumber);
-- Perform the cleanup
DELETE
FROM CallProcessingRecord
WHERE sha1 NOT IN (SELECT distinct(cpr_sha1) FROM CallRoutingNumber;
The script assumes that the CRN entries are stored in the CallRoutingNumber table, the CPR entries are stored in the CallProcessingRecord table and there is a foreign key reference between the CallRoutingNumber and CallProcessingRecord tables (column “cpr_sha1” in CallRoutingNumber table references the “sha1” primary key in CallProcessingRecord table). The script records the total number of orphaned CPRs prior to removing the offending entries.
It is possible that there could be thousands of orphaned CPRs if the cleanup has not been done for some time. It is recommended to clean up the orphaned CPRs in batches of 50,000 at a time if there are more than 50,000 of them. The above “DELETE” SQL script can be updated as below to perform the cleanup in batches of 50,000 at a time.
DELETE
FROM CallProcessingRecord
WHERE sha1 NOT IN (SELECT distinct(cpr_sha1) FROM CallRoutingNumber) ORDER BY id ASC LIMIT 50000;
The following command can be used to invoke this script (assuming the above script is saved in a file named OrphanCprCleanup.sql and using MySQL) from the command line capturing (appending) the output to a log file:
mysql -sN -h <db host> -P 3306 -u <username> -p <db name> < OrphanCprCleanup.sql >> <log file name>