In enterprise environments, it is common to host multiple Pega applications on a shared application server and database, often spanning across various lines of business (LOBs). A scenario may arise where a specific LOB is divested or restructured as an independent entity. This necessitates the separation of its Pega application and associated data from the existing infrastructure.

Use Case Overview

Consider an environment hosting two Pega applications: LOB A and LOB B. If LOB B is being spun off into a separate organization, it must migrate to a new Pega instance. This migration involves:

• Extracting and transferring large volumes of data specific to LOB B.
• Ensuring no data from LOB A is shared.
• Deleting all LOB B-related data from the shared environment post-migration.

Key Challenges

1. Data Identification:
   • Precisely identify and isolate case data related to LOB B for migration.
   • Identify and delete all case data related to LOB A from the shared environment.
2. Data Volume:
   • Manage deletion of high-volume data (1 TB+).
3. Time Constraints:
   • Complete the migration and cleanup within a 24-hour window over a weekend to ensure zero impact on business operations.
4. Data Integrity and Compliance:
   • Ensure no cross-contamination of data between LOBs.
   • Validate data deletion and migration through business user sign-off.

Strategic Decision

Given the tight timeline and volume of data, the team opted for SQL-based deletion scripts to expedite the process. The Database Administrator (DBA) played a critical role in optimizing these scripts for performance and accuracy.

1. Shutdown all production nodes.
2. Take a full backup of the production database.
3. Restore the backup in a temporary environment.
4. Execute deletion scripts to remove LOB A data.
5. Validate deletion with LOB A business users and obtain sign-off.
6. Backup the cleaned database, now containing only LOB B data.
7. Restore the cleaned backup in the new production environment for LOB B.
8. Validate data integrity in the new environment and obtain LOB B sign-off.
9. Disable all LOB B users in the original environment.
10. Remove authentication profiles and stop background processing for LOB B in the original environment.

A reverse deletion strategy was planned to remove LOB B data from the original environment after a few weeks, ensuring risk mitigation and rollback capability.

Analysis and Planning

• Review all case types, indexes, and data storage strategies used by LOB B.
• Identify:
  • Tables exclusive to LOB B.
  • Tables shared between LOB A and B (requiring conditional deletion).
• Exclude Pega rules tables from deletion.

Deletion Execution

• Truncate rows in LOB B-specific tables.
• Use conditional SQL queries for shared tables.

Sample SQL Queries for Shared Tables

SELECT * FROM pega_data.pc_data_workattach where pxRefObjectKey like 'LOB-B-Class-Pattern%' ;

SELECT * FROM pega_data.pc_assign_workbasket where pxRefObjectClass like 'LOB-B-Class-Pattern%' ;

SELECT * FROM pega_data.pc_assign_worklist where pxRefObjectClass like 'LOB-B-Class-Pattern%' ;

SELECT * FROM pega_data.pc_index_workparty where pxInsIndexedClass like 'LOB-B-Class-Pattern%';

SELECT * FROM pega_data.pc_link_attachment where pxLinkedClassFrom like 'LOB-B-Class-Pattern%' ;

SELECT * FROM pega_data.pc_link_folder where pxLinkedClassFrom like 'LOB-B-Class-Pattern%' ;

SELECT * FROM pega_data.pc_work_social where pxApplication='LOB-B' ;

SELECT * FROM pega_data.paf_index_workparty where pxInsIndexedClass like 'LOB-B-Class-Pattern%'

SELECT * FROM pega_data.pc_work_notification where pxContextClass like 'LOB-B-Class-Pattern%'

SELECT * FROM pega_data.pca_index_interactions where pxInsIndexedClass like 'LOB-B-Class-Pattern%'

SELECT * FROM pega_data.PCA_INDEX_RELATEDINTERACTIONS where pxInsIndexedClass like 'LOB-B-Class-Pattern%'

SELECT * FROM pega_data.pca_nps_survey where  pxApplication='LOB-B';

SELECT * FROM pega_data.pr_assign where  pxRefObjectClass like 'LOB-B-Class-Pattern%';

SELECT * FROM pega_data.pr_hourly_usage  where  pxApplication='LOB-B';

SELECT * FROM pega_data.pr_sys_queues  where  pxObjClass='LOB-B-Class-Pattern';

SELECT * FROM pega_data.pr_daily_usage where pzUserID in ( select pyUserIdentifier from pega_data.pr_operators where pyAccessGroup like 'LOB-B%' );

SELECT * FROM pega_data.fw_index_operemail where pxInsIndexedKey in ( select pzInsKey from pega_data.pr_operators where pyAccessGroup like 'LOB-B%' );

This strategy enabled a successful and timely separation of LOB B’s Pega application and data. The key to success lay in:

• Clear identification of what data to retain, delete, and migrate.
• Efficient execution within a tight timeframe.
• Close collaboration between development, DBA, and business teams.

This approach can serve as a reference for similar Pega application separation projects in complex enterprise environments.