SQL upgrade scripts

Use SQL upgrade scripts to upgrade your installation of Campaign according to your database type.

The SQL upgrade scripts are stored in the Campaign_Home/tools/upgrade/8.5+To9.1 directory. Use one of the scripts from the following table according to your database type:

SQL upgrade scripts according to database type
SQL upgrade script	Database type
ac_upgrade_db2.sql	DB2® upgrade script (non-Unicode)
ac_upgrade_db2_unicode.sql	DB2® upgrade script (Unicode)
ac_upgrade_oracle.sql	Oracle upgrade script (non-Unicode)
ac_upgrade_oracle_unicode.sql	Oracle upgrade script (Unicode)
ac_upgrade_sqlsvr.sql	MS SQL Server upgrade script (non-Unicode)
ac_upgrade_sqlsvr_unicode.sql	MS SQL Server upgrade script (Unicode)

Changes to SQL upgrade scripts

You must modify the SQL upgrade scripts to reflect the changes that are made to the Campaign database table. Use the following table to understand the changes that must be done for some SQL upgrade scripts:

Changes to SQL upgrade scripts

Changed Campaign database table name

Required change in SQL upgrade scripts

UA_ContactHistory table

In your existing Campaign environment, the CustomerID field in the UA_ContactHistory table has been changed to ID.

To accommodate the field name change, change all occurrences of CustomerID to ID in the upgrade scripts.

HH_ContactHistory

HH_ResponseHistory

HH_DtlContactHist

Your existing Campaign environment contains an additional audience level called Household. To support the audience level, your database contains the HH_ContactHistory, HH_ResponseHistory, and HH_DtlContactHist tabes. The primary key is HouseholdID.

Complete the following tasks to support the Household audience level in your new Campaign installation:

Locate the code in the SQL upgrade script that updates response history and treatment sizes for the Customer audience level.

Replicate the code for your Household audience level.

Change the table names in the statements to the appropriate names for your Household audience level, and change the references of CustomerID to HouseholdID.

The following example SQL statements show the required additions that must be made to the ac_upgrade_sqlsvr.sql script for an SQL Server database that contains the Household audience level. The text that has been changed to support the Household audience level is in bold:


-- ResponseHistory update "template"
ALTER TABLE HH_ResponseHistory ADD DirectResponse int NULL
go
	
-- Update the treatment sizes

update ua_treatment 
	set treatmentsize=(select count(DISTINCT HouseholdID) 
	from HH_ContactHistory 
	where HH_ContactHistory.CellID = ua_treatment.CellID 
		AND HH_ContactHistory.PackageID = ua_treatment.PackageID 
		and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 0)

where exists
	
(select * from hh_contacthistory 
	where hh_contacthistory.CellID = ua_treatment.CellID 
	AND hh_contacthistory.PackageID = ua_treatment.PackageID 
	and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 0)
go
	
update ua_treatment 
	set treatmentsize=(select count(DISTINCT HouseholdID) 
	from HH_DtlContactHist 
	where HH_DtlContactHist.TreatmentInstID = ua_treatment.TreatmentInstID 
	and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 1)

where exists
	
(select * from hh_dtlcontacthist 
	where hh_dtlcontacthist.TreatmentInstID = ua_treatment.TreatmentInstID 
	and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 1)
go

For more information on managing database tables and audience levels, see the IBM® Campaign Administrator's Guide.