For eMessage reports, limiting scope of delta processing

The 9.1 eMessage reports pack has improved performance that partly relies on the newly introduced staging tables that are associated with the current eMessage system tables.

For more information about schema changes, see IBM® eMessage 9.1 System Tables and Data Dictionary. The initial set up for the eMessage stored procedures relies on the following new database scripts:

acer_indexes_dbname.sql

acer_scripts_dbname.sql

acer_tables_dbname.sql

The database scripts are located in the Campaign_reportspack_home\cognos10\emessage-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 mailing data to populate the staging tables. The batch procedures must be run on a regular basis to populate the staging tables.

The initial runs of the eMessage stored procedures can take a long time to complete, depending on the amount of data 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. You can change the default value before or after you run the SQL scripts for eMessage. For more information on running the SQL scripts, see For eMessage reports, creating stored procedures staging tables, and indexes

The following example illustrates the changes that you can make to the acer_tables script to limit the processing to the previous 30 days, if your database is Oracle:

The changes include modifying the UARE_MASTER_MAILING 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 for the use case described above (Oracle database)

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 the available report data again, modify the UARE_MAILING_MASTER view to remove the date filter from the view and refresh all Oracle or DB2® materialized views again. For example, in the sample view creation shown above, remove the following lines:

UCC_CONTAINER.CREATED >= sysdate - 30