Data purging

WSO2 Open Banking databases store data for different open banking requirements. As the volume of the data stored grows over time, It is highly recommended to perform data purging on servers to mitigate performance issues.

Based on the use cases the data in the database might grow fast sometimes and cleaning them up from the product itself becomes expensive. This page explains how to perform data purging by clearing consent and event notification related data using stored procedures. These offload the expensive data cleanups to the database server.

1. Back up all running databases.

2. Set up the database dump in a test environment and test it for any issues.

   Note

   We recommend that you test the database dump before the cleanup task as the cleanup can take some time.

3. According to your database type, execute the store procedures in the <OB_IS_ACCELERATOR_HOME>/carbon-home/dbscripts/stored-procedures directory.

4. Once the cleanup is over, start WSO2 Servers by pointing them to the cleaned-up database dump and test thoroughly for any issues.

Consent Cleanup¶

Consent cleanup is performed via the consent-cleanup.sql script. For configurable attributes, see the Common Configurable Attributes section.

1. According to your database type, compile the stored procedure in the <OB_IS_ACCELERATOR_HOME>/carbon-home/dbscripts/stored-procedures/consent-purging-procedures/<DB-Type>/<DB-Type>-consent-cleanup-script.sql file.

2. Execute the compiled store procedure.

3. Configure according to the following:

   Click here to see the Logical condition and configurations used for Consent data purging...

   Consent Data Purging Parameters

   Parameter	Description
   consentTypes	consent_types that are eligible for purging. For example, accounts, payments, to skip leave it empty
   clientIds	client_ids that are eligible for purging. To skip this, leave it empty.
   consentStatuses	consent_statuses that are eligible for purging. For example, expired,revoked
   purgeConsentsOlderThanXNumberOfDays	The time period to delete consents older than n days.
   lastUpdatedTime	last_updated_time for purging. If the consent updated time is older than this value then it's eligible for purging To purge consents with the last updated_time older than 31 days, configure the following value in days purgeConsentsOlderThanXNumberOfDays = 31. To configure exact timestamp of the lastUpdatedTime rather than a time period, ignore configuring purgeConsentsOlderThanXNumberOfDays = NULL and only configure lastUpdatedTime with the exact unix timestamp. For example, lastupdatedtime = 1660737878;

   Other Parameters

   Parameter	Description
   backupTables	Enable the backup table to restore data at a later stage. This backup tables is overwritten every time you run the cleanup script. This does not capture the consents that were created between the backup task and the cleanup iteration. Therefore, if consents are created after the backup task, you cannot restore them once they are deleted from the cleanup iteration.
   enableAudit	Set this parameter to true to log each deleted consents in the auditlog_ob_consent_cleanup table in order to track them in future.

4. Once the stored procedure is compiled, execute the procedure with input parameters as follows:

   MySQL

   WSO2_OB_CONSENT_CLEANUP_SP( consentTypes, clientIds, consentStatuses, purgeConsentsOlderThanXNumberOfDays, 
   lastUpdatedTime, backupTables, enableAudit, analyzeTables );
   
   mysql> call WSO2_OB_CONSENT_CLEANUP_SP('accounts,payments', 'clientId1,clientId2', 'expired,revoked', 31, NULL,
   TRUE, TRUE, TRUE);

5. For more information, see the /stored-procedures/consent-purging-procedures/<DB-Type>/readme.MD file for each database type.

Event Notification Cleanup¶

Event Notification cleanup is performed via the event-notification-cleanup.sql script. For configurable attributes, see the Common Configurable Attributes section.

1. According to your database type, compile the stored procedure in the <OB_IS_ACCELERATOR_HOME>/carbon-home/dbscripts/stored-procedures/event-notification-purging-procedures/<DB-Type>/<DB-Type>-event-notification-cleanup-script.sql file.

2. Execute the compiled store procedure.

3. Configure according to the following:

   Click here to see the Logical condition and configurations used for Event Notification data purging...

   Event Notification Data Purging Parameters

   Parameter	Description
   eventStatuses	The event statuses that are eligible for purging. For example, ACK,ERR
   clientIds	client_ids that are eligible for purging. To skip this, leave it empty.
   purgeEventsOlderThanXNumberOfDays	The time period to delete event-notifications older than n days.
   lastUpdatedTime	last_updated_time for purging. If the event-notification updated time is older than this value then it's eligible for purging
   purgeNonExistingResourceIds	The flag to enable purging of untraceable event notifications. If the resource_id of the ob_notification does not exist in the ob_consent table - consent_id, then it is untraceable and eligible for purging Set this to true, to purge the untraceable event notifications irrespective of other purging parameters. To purge event-notifications with the last updated_time older than 31 days, configure the following value in days purgeEventsOlderThanXNumberOfDays = 31. To configure exact timestamp of the lastUpdatedTime rather than a time period, ignore configuring purgeEventsOlderThanXNumberOfDays = NULL and only configure lastUpdatedTime with the exact unix timestamp. For example, lastupdatedtime = 1660737878;

   Other Parameters

   Parameter	Description
   backupTables	Enable the backup table to restore data at a later stage. This backup tables is overwritten every time you run the cleanup script. This does not capture the event notifications that were created between the backup task and the cleanup iteration. Therefore, if event notifications are created after the backup task, you cannot restore them once they are deleted from the cleanup iteration.
   enableAudit	Set this parameter to true to log each deleted consents in the auditlog_ob_event_notification_cleanup table in order to track them in future.

4. Once the stored procedure is compiled, execute the procedure with input parameters as follows:

   MySQL

   WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP( eventStatuses, clientIds, purgeEventsOlderThanXNumberOfDays, lastUpdatedTime,
   purgeNonExistingResourceIds, backupTables, enableAudit, analyzeTables );
   
   mysql> call WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP('ACK,ERR', 'clientId1,clientId2', 10, NULL, TRUE, TRUE, TRUE, TRUE);

5. For more information, see the /stored-procedures/event-notification-purging-procedures/<DB-Type>/readme.MD file for each database type.

Common Configurable Attributes¶

You can configure the following important attributes in the script.

Restore Script for Consent and Event-Notification data¶

• The <DB>-consent-restore-script.sql and <DB>-event-notification-restore-script.sql contain the stored procedures that are used to restore the purged consents and event notifications from the consent tables respectively.
• The restoration can only be performed if the backupTablesproperty is set to true in the data purging procedures.
• This is only an immediate restoration script for the data purging procedures, therefore each execution of the WSO2_OB_CONSENT_CLEANUP_SP and WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP procedures will replace the backup tables.

• You can schedule a cleanup task to automatically run after a given period of time, for example:

  MySQL

  USE 'WSO2_OB_CONSENT_DB';
  DROP EVENT IF EXISTS consent_cleanup;
  CREATE EVENT consent_cleanup
      ON SCHEDULE
          EVERY 1 WEEK STARTS '2015-01-01 00:00.00'
      DO
          CALL `WSO2_OB_CONSENT_DB`.WSO2_OB_CONSENT_CLEANUP_SP('accounts,payments', 'clientId1,clientId2', 'expired,revoked', 31, NULL, TRUE, TRUE, TRUE);
  
  -- 'Turn on the event_scheduler'
  SET GLOBAL event_scheduler = ON;