This issue has been documented as ERQ CLRT-15367
Any updates on CA recommened ways to bulk delete records from CLB_NOTIFICATIONS table?
You can try below but please take the back up before running the delete
WHERE created_date <(GETDATE()-15)
WHERE created_date <SYSDATE-15)
replace this "(GETDATE()/SYSDATE-15)" with number of days you want to keep the records or purge the entire table if you don't want to keep any notifications.
Thats it? A simple delete from the notification table? But the Tech Ref Guide shows that CLB_NOTIFICATION_ASSOCS table is also linked to this notifications table as below (CLB_NOTIFICATION_ASSOCS.INSTANCE_ID=CLB_NOTIFICATIONS.ID).
So just confused!!!
Yes the ID of both the table have a reference and if you delete from notification there would be a record left in CLB_NOTIFICATION_ASSOCS but as of now we delete notification using the query which i mentioned,
Thanks a lot for the info. But in doing so, we will only be creating so many orphan records in CLB_NOTIFICATION_ASSOCS table, right? Even if CA creates a job in future to purge the records from both the notification tables, these orphans might get excluded from that purge job too which we dont want Thats why we planned to delete those matching records from CLB_NOTIFICATION_ASSOCS table too.
But before that, we just wanted to confirm whether these are the only 2 tables linking to a clarity notification record or are there more tables? If yes, can you please give us the list so that we can do a clean delete joining them, without leaving unnecessary orphan records in Clarity DB?
Obviously this is not out of box functionality and orphan in CLB_NOTIFICATION_ASSOCS don't create any issue so we delete only from CLB Notifications.
We understand it wont create any issues now, but we just wanted to avoid these orphan records creating any issue in future too
Compared to the 16 million records in the notifications table, our CLB_NOTIFICATION_ASSOCS table only had 4K records. So we had the below two options to clear the Notification table (all noifications before last 30 days), without creating orphans in the Database. We chose the 2nd option.
DELETE FROM CLB_NOTIFICATIONS WHERE ID NOT IN (SELECT INSTANCE_ID FROM CLB_NOTIFICATION_ASSOCS) AND CREATED_DATE < SYSDATE-30;
DELETE FROM CLB_NOTIFICATIONS WHERE CREATED_DATE < SYSDATE-30;
DELETE FROM CLB_NOTIFICATION_ASSOCS WHERE INSTANCE_ID NOT IN (SELECT ID FROM CLB_NOTIFICATIONS);
Thanks a lot,
Hi Suman, we are following same delete statement to get rid these notification logs. However, just would like to know, is there any XOG option to perform this activity ? We know, deletion through xog is possible by setting 'compeleted = true' or 'delete = true' in xog script for other purposes ( like deleting rights , groups, obs or deleting team member with '0 actual' from project allocation. If anything please let us know.
Notification is not an object and hence XOG will not work here, the plan is to have a job to clear out but its still not there in the product, so at this moment its best to use the delete script.
Thanks for the reply. However, isn't notification an object in v13.3 ? We are still not there..however, heard of that..
"However, isn't notification an object in v13.3 ? We are still not there..however, heard of that.." - Don't think so, though you have notification templates -
Retrieving data ...