Unica Platform and Campaign system database migration using Campaign flowchart execution

There is a situation where enterprise decides to move away from one database vendor and adopt another database vendor as their preferred choice for applications they are using enterprise-wide. We came across one such situation where one customer in North America decided to move away from IBM DB2 and adopt MS SQL server as their preferred choice for all enterprise applications across their organization; they can then use the procedure mentioned in this blog which involves a system database migration using Campaign flowcharts.

The Goal

Migrated Unica Platform/Campaign system database from one DB type to another using Campaign flowchart execution from the source environment. (Here, the goal is to migrate the Unica Campaign system DB IBM DB2 to MS SQL Server)

Challenge

The customer did not have the financial budget to purchase a license for database migration tools available in the market.

Solution Module

We have used our DB migration methodology for Unica Platform and Campaign modules. But the same method can be used for the rest of the Unica suite.

Platform module is used for creating and storing the security policies required for the Unica application. The Platform module stores the configuration settings of different Unica modules/applications in the suite.

Unica Campaign is the advanced campaign management solution that delivers customer targeting at scale with powerful segmentation capabilities for outbound, multi-channel, multi-wave batch campaign execution and tracking.

Key Feature

The key feature of this methodology is to showcase how Campaign flowchart execution can be used to migrate Unica Platform and Campaign system tables from the existing IBM DB 2 database to the MS SQL server database.

STEP-BY-STEP SOLUTION

1. Overview of Unica System DB Migration

We have implemented Unica System DB Migration from IBM DB2 to MS SQL Server using Unica Campaign flowcharts in this POC. The below diagram depicts the approach used for Unica System DB Migration.

2. Configuration of Unica Environments

2.1 Source Environment

• Unica v9.1.1
• Windows 
• IBM DB2 
• database Name :- Dexter (plat911,camp911)
• IBM Websphere

2.2 Target Environment

• Unica v9.1.1
• Windows 
• MS SQL Server 2008 R2
• Database :- Plat911 (dbo) and Camp911(dbo)
• IBM Websphere

Note: The target Unica system was shut down by stopping the Campaign listener and application server (Websphere) before we started exporting the system table data through the execution of Campaign flowcharts.

2.3 Pre-Tasks (Source Unica environment)

2.3.1 Create a data source pointing to Source Platform system tables

Create a user data source named ‘PlatTables’ at following Platform configuration path, which points to schema plat911 of source DB2 database containing Platform system tables. Affinium|Campaign|partitions|partition1|dataSources|PlatTables.

2.3.2 Create a data source pointing to Source Campaign system tables

Create a user data source named ‘CampTables’ at following Platform configuration path, which points to schema camp911 of source DB2 database containing Campaign system tables
Affinium|Campaign|partitions|partition1|dataSources|CampTables

2.3.3 Create a data source pointing to Target Campaign system tables

Create a user data source named ‘MSCamp’ at following Platform configuration path which points to database Camp911 of target SQL Server database containing Campaign system tables

Affinium|Campaign|partitions|partition1|dataSources|MSCamp

2.3.4 Create a data source pointing to Target Platform system tables

Create a user data source named ‘MSPlat’ at following Platform configuration path which points to database Camp911 of target SQL Server database containing Campaign system tables

Affinium|Campaign|partitions|partition1|dataSources|MSPlat

2.4 Unica system table migration

1. Disable all Constraints in Target MS SQL Server Database for Both Platform and Campaign system tables

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

2. Truncate all data from all tables in Platform USM_* and Campaign UA_* tables in Target MS SQL Server Database

delete from <table_name> ;

3. Create batch flowchart in Source Unica application as displayed in this section.

Note: Perform the below steps for all Platform USM_* and Campaign UA_* tables in individual batch flowcharts

3.1 Select the Unica platform system table from the Source data source (Plat911)

3.2 Select 1 Platform Table from DB2 and click Next

3.3  Select the target table from target data source MSPlat in MS SQL Server for migrating data.

3.4 Save and Run the flow chart.

Notes: Check by querying the table in MS SQL Server; the record count should match in both source and target database (i.e., DB2 and MS SQL Server database). If errors are found, check the troubleshooting section.

3.5. Export all possible Unica Platform tables from source DB2 data source to target MS SQL server data source by creating and executing flowcharts as shown above.

6. Repeat the above procedure for Campaign system tables. Select Campaign system table from a source data source (DB2) ‘CampTables’ and export the same to a target data source (SQL Server) named ‘MSCamp’ as shown in the below screenshot.

2.5 Troubleshooting

7. Truncate table from Target Database before Migration fails

Issue : delete from Plat911.dbo.USM_USER

Error Message : The DELETE statement conflicted with the REFERENCE constraint “FK_USM_DBA_USER”. The conflict occurred in database “Plat911”, table “dbo.USM_DB_ACCESS”, column ‘USER_ID’.

The statement has been terminated.

Fix :

• ALTER TABLE Plat911.dbo.USM_USER_ROLE_MAP
  NOCHECK CONSTRAINT FK_USM_UR_MAP_USER;
  ALTER TABLE Plat911.dbo.USM_DB_ACCESS
  NOCHECK CONSTRAINT FK_USM_DBA_USER;
  ALTER TABLE Plat911.dbo.USM_PERSONALIZATION
  NOCHECK CONSTRAINT FK_USER_ID;
• Create a new dummy table Plat911.dbo.USM_USER_test without any constraints
• Create a flow chart and use Base Record Table for data migration of both source and target tables. For Target table, specify the new table name that was created in step b
• SET IDENTITY_INSERT Plat911.dbo.USM_user On
• insert into [dbo].[USM_USER] by selecting columns from dbo.USM_USER_test
• ALTER TABLE Plat911.dbo.USM_USER_ROLE_MAP
  CHECK CONSTRAINT FK_USM_UR_MAP_USER;ALTER TABLE Plat911.dbo.USM_DB_ACCESS
  CHECK CONSTRAINT FK_USM_DBA_USER;ALTER TABLE Plat911.dbo.USM_PERSONALIZATION
  CHECK CONSTRAINT FK_USER_ID;
• Drop the dummy table created Plat911.dbo.USM_USER_test

8. Identity insert issue on the target database (i.e., MS SQL Server DB)

Issue: Flow Chart fails to migrate data due to identity insert issue

Fix : 

• Create a new dummy table USM_OBJECT_TYPE_NEWTEST without any constraints
• Create a flow chart and use the Base Record Table for data migration of both source and target tables. For the Target table, specify the new table name that was created in step b
• SET IDENTITY_INSERT Plat911.dbo.USM_OBJECT_TYPE On
• insert into [dbo].[ USM_OBJECT_TYPE] by selecting columns from dbo.USM_Object_Type_NEWTEST
• Drop the dummy table created dbo.USM_Object_Type_NEWTEST

3. Post-migration configuration

3.1 Database updates in Target database (MS SQL SERVER)

Note: Ensure that the application server (WAS/WL) and Campaign listener on the target Campaign environment are stopped.

Update the string_value column in target Platform Database table USM_CONFIGURATION_VALUES to https://<targetHost>:<TargetPort>/unica as shown below.

3.2 DataSource in Target Unica environment

• Create a new UA_SYSTEM_TABLES data source in the MS SQL server database template
• Delete the UA_SYSTEM_TABLES data source from the target Unica system as this is exported from the Source DB2 system, and the type of the DB template is of DB2 and points to DB2 source Campaign schema.
• Create a new UA_SYSTEM_TABLES data source using SQL Server template and point it to Target Campaign system database.

3.3 Change the serverURL for Campaign in Target environment’s Platform configuration

Login in into Target Unica application URL and navigate to the following Platform configuration tree path
Affinium|Campaign|navigation

And update the value of setting serverURL to target system’s host: port as seen below.

3.4 Copy the source system’s partition1 file system

Copy the source system’s partition1 file system over to the target environment. Take a backup of the target Campaign application’s partition1 folder and restore the source Campaign application’s partition1 as shown below.

•  Log out of the target Unica application and start the Unica listener.

•  Login into the target Unica application again and verify the flowcharts are correctly imported.

Note – This Platform and Campaign system database migration process can be used for any version of Unica. For POC purposes we have used Unica version 9.1.1.

To learn more about system database migration using Campaign flowcharts, you can reach out to us, and we will be happy to help.