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.

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.

Unica Campaign DB

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

Unica CampTable

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

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

Unica MS Plat

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)

Unica Campaign Process Configuration

Unica Campaign- New Table

3.2 Select 1 Platform Table from DB2 and click Next

Unica Campaign Platform Table

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

Migrating data for Unica Campaign

 

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.

Unica Campaign

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.

Comment wrap
Further Reading
article-img
Marketing & Commerce | October 13, 2021
Offer Integration with HCL Unica Journey
Customer insights help companies apply the Next Best offer that is suitable for them. Complete understanding of your customer lets you take a customer-centric approach.
article-img
Marketing & Commerce | September 16, 2021
9 features of Unica V12 that got us in the Constellation ShortList
HCL Software is excited to announce that Unica has been shortlisted for Constellation ShortList report along with Salesforce, Adobe & others.
article-img
Marketing & Commerce | August 12, 2021
HCL Unica Campaign Integration with Apache ® Kafka
Apache Kafka is an open-source distributed event streaming platform used for high-performance data pipelines, streaming analytics and many more. Let's learn about its integration with Unica Campaign.
Close