Reviewing and modifying the SQL upgrade script

If your Interact runtime environment includes customizations to the runtime system tables that modified the default Data Definition Language (DDL) included with Interact, you must modify the default SQL upgrade script for your database to match your customizations.

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 IBM® 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.

You must complete the changes before you run the Interact upgrade tool.

Complete the following steps to review and modify the SQL upgrade script:

Locate the upgrade script for your database type. The scripts are installed in the /ddl/Upgrades or /ddl/Upgrades/Unicode directory under your Interact installation after you run the IBM® EMM installer in upgrade mode.

Ensure that your database schema matches the Data Definition Language (DDL) included with 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 Interact design time environment contains an additional audience level called Household. To support the Household audience level, your 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