Reviewing and modifying the SQL upgrade script
If your Unica Interact runtime environment includes customizations to the runtime system tables that modified the default Data Definition Language (DDL) included with Unica Interact, you must modify the default SQL upgrade script for your database to match your customizations.
About this task
Common customizations include changes to support multiple audience levels or using views of tables. You can review the data dictionaries for the new versions of products to confirm that column sizes map correctly and that foreign key constraints from additional products do not conflict.
The aci_runtab_upgrd and the aci_usrtab_upgrd are the SQL upgrade scripts that most likely require revisions.
Complete the following steps to review and modify the SQL upgrade script:
Procedure
- Locate the upgrade script for your database type. The scripts are installed in the /ddl/Upgrades or /ddl/Upgrades/Unicode directory under your Unica Interact installation after you run the Unica installer in upgrade mode.
- Ensure that your database schema matches the Data Definition
Language (DDL) included with Unica Interact. If
your database schema does not match the DDL in the upgrade script,
edit the script for your database type to match your environment.
The following example shows the required modifications to the aci_runtab_upgrd SQL upgrade script to support the Household audience level:
Your existing Unica Interact design time environment contains an additional audience level called Household. To support the Household audience level, your Unica Interact runtime environment database contains tables named HH_CHStaging and HH_RHStaging.
Required changes to the upgrade script:
- Locate the code in the SQL upgrade script that updates the response history and treatment sizes for the Customer audience level and replicate it for your Household audience level. Change the table names in the SQL statements to the appropriate names for your Household audience level.
- You must also revise the SQL script to support the data type change for the SeqNum column in the UACI_RHStaging table. The value of the SeqNum is a sequential number across all response history staging tables. The next value that is used is tracked by the NextID column in the UACI_IdsByType table, where TypeID is 2. For example, you have three audience levels, customer, household, and account. In the customer response history staging table, the highest SeqNum is 50. In the household response history staging table, the highest SeqNum is 75. In the account response history staging table, the highest SeqNum is 100. Therefore, you must alter the SQL to set the NextID for TypeID = 2 in the UACI_IdsByType to 101.
The following example SQL statements show the required additions to the aci_runtab_upgrd_sqlsvr.sql script for a SQL Server database that contains the Household audience level. The text that is added to support the Household audience level is in bold:
ALTER TABLE UACI_CHStaging ADD RTSelectionMethod int NULL go ALTER TABLE UACI_RHStaging ADD RTSelectionMethod int NULL go ALTER TABLE HH_CHStaging ADD RTSelectionMethod int NULL go ALTER TABLE HH_RHStaging ADD RTSelectionMethod int NULL go insert into UACI_IdsByType (TypeID, NextID) (select 2, IDENT_CURRENT('UACI_RHStaging') + IDENT_CURRENT('HH_RHStaging') + IDENT_INCR( 'UACI_RHStaging' )) go select * into UACI_RHStaging_COPY from UACI_RHStaging go select * into HH_RHStaging_COPY from HH_RHStaging go DROP TABLE UACI_RHStaging go CREATE TABLE UACI_RHStaging ( SeqNum bigint NOT NULL, TreatmentCode varchar(512) NULL, CustomerID bigint NULL, ResponseDate datetime NULL, ResponseType int NULL, ResponseTypeCode varchar(64) NULL, Mark bigint NOT NULL DEFAULT 0, UserDefinedFields char(18) NULL, RTSelectionMethod int NULL, CONSTRAINT iRHStaging_PK PRIMARY KEY (SeqNum ASC) ) go insert into UACI_RHStaging (SeqNum, TreatmentCode, CustomerID, ResponseDate, ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod) (select SeqNum, TreatmentCode, CustomerID, ResponseDate, ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod from UACI_RHStaging_COPY) go DROP TABLE UACI_RHStaging_COPY go DROP TABLE HH_RHStaging go CREATE TABLE HH_RHStaging ( SeqNum bigint NOT NULL, TreatmentCode varchar(512) NULL, HouseholdID bigint NULL, ResponseDate datetime NULL, ResponseType int NULL, ResponseTypeCode varchar(64) NULL, Mark bigint NOT NULL DEFAULT 0, UserDefinedFields char(18) NULL, RTSelectionMethod int NULL, CONSTRAINT iRHStaging_PK PRIMARY KEY (SeqNum ASC) ) go insert into HH_RHStaging (SeqNum, TreatmentCode, HouseHoldID, ResponseDate, ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod) (select SeqNum, TreatmentCode, HouseHoldID, ResponseDate, ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod from HH_RHStaging_COPY) go DROP TABLE HH_RHStaging_COPY go
For DB2® and Oracle databases, the following statement is used for inserting values into the UACI_IdsByType table:
INSERT into UACI_IdsByType (TypeID, NextID) (select 2, COALESCE(max(a.seqnum)+1,1) + COALESCE(max(b.seqnum)+1,1) from UACI_RHSTAGING a, ACCT_UACI_RHSTAGING b );
If you have multiple audiences, you must add the following sections to the aci_usrtab_upgrd SQL script for each audience level:
ALTER TABLE HH_ScoreOverride ADD OverrideTypeID int NULL, CellCode varchar(64) NULL, Zone varchar(64) NULL go ALTER TABLE HH_ScoreOverride ADD Predicate varchar(4000) NULL, FinalScore float NULL, EnableStateID int NULL go CREATE INDEX iScoreOverride_IX1 ON HH_ScoreOverride ( HouseHoldID ASC ) go