For Unica Deliver only: Stored procedures for delta processing

Unica Deliver reports require staging tables that are associated with the Unica Deliver system tables. The system tables are part of the Unica Campaign schema. You must periodically run stored procedures to process message response data for use in Unica Deliver reports.

For more information about schema changes, see Unica Deliver System Tables and Data Dictionary.

The initial setup for the Unica Deliver stored procedures relies on the following database scripts:

  • acer_indexes_dbname.sql
  • acer_tables_dbname.sql
  • acer_scripts_dbname.sql
Note: If you observe any issues while executing the mentioned sql files, use the script terminator based on the database client. If your database client shows errors for acer_scripts_dbname.sql, create the procedures one after the other.

The database scripts are in the Campaign\reports\Deliver-ddl directory for the Oracle, IBM® DB2®, and Microsoft SQL Server databases.

The scripts set up indexes, tables, views, and stored procedures. The stored procedures refresh message data to populate the staging tables. The batch procedures must be run regularly to populate the staging tables. Running the stored procedures is referred to as delta processing.

The initial runs of the Unica Deliver stored procedures can take a long time to complete, depending on the amount of data that is contained in your tables. Subsequent delta processing also can take a long time to complete. You can significantly reduce the processing time by limiting the number of mailing instances (containers) that are processed by the stored procedures.

By default, data is processed for the past 90 days. However, you can change the default value before or after you run the SQL scripts for Unica Deliver.

Example for Oracle

The following examples for an Oracle database illustrate the changes that you can make to the acer_tables script to limit processing to the previous 30 days:
Note: The changes include modifying the UARE_MAILING_MASTER view.

Definition of the current view

CREATE VIEW UARE_MAILING_MASTER AS
(
(SELECT UCC_CONTAINER.CAMPAIGNID,UCC_CONTAINER.CONTAINERID,
substr(UCC_CONTAINERATTR.STRINGVALUE,1,100) AS CAMPAIGN_NAME,
UCC_CONTAINER.CONTAINERNAME AS MAILING_INST,
UCC_CONTAINER.CREATED AS MAILING_CREATED,
UCC_CONTAINER.CONTAINERTYPEID CONTAINERTYPEID,
UCC_CONTAINER.CONTCHANNELTYPEID CONTCHANNELTYPEID
FROM
UCC_CONTAINER,UCC_CONTAINERATTR
WHERE
UCC_CONTAINERATTR.CONTAINERID=UCC_CONTAINER.CONTAINERID AND
UCC_CONTAINERATTR.ATTRIBUTENAME='CampaignName' AND
UCC_CONTAINER.CREATED >= sysdate - 91
)

Definition of the modified view

CREATE VIEW UARE_MAILING_MASTER AS 
(
SELECT UCC_CONTAINER.CAMPAIGNID, UCC_CONTAINER.CONTAINERID,
substr(UCC_CONTAINERATTR.STRINGVALUE,1,100) AS CAMPAIGN_NAME, 
UCC_CONTAINER.CONTAINERNAME AS MAILING_INST, UCC_CONTAINER.CREATED AS 
MAILING_CREATED FROM UCC_CONTAINER,UCC_CONTAINERATTR WHERE 
UCC_CONTAINERATTR.CONTAINERID=UCC_CONTAINER.CONTAINERID AND 
UCC_CONTAINERATTR.ATTRIBUTENAME='CampaignName' 
AND 
UCC_CONTAINER.CREATED >= sysdate - 30
)

To view all available report data, modify the UARE_MAILING_MASTER view to remove the date filter from the view. Then, refresh all Oracle or DB2® materialized views. For example, in the sample view creation that is shown above, remove the following line:

UCC_CONTAINER.CREATED >= sysdate - 30