For eMessage reports, creating stored procedures, staging tables, and indexes

Before you render eMessage reports, run specific SQL scripts after you install or upgrade reporting templates. The SQL scripts create stored procedures and staging tables.

About this task

The Campaign_reportspack_home\cognos10\emessage-ddl directory is on the server that hosts the IBM® Cognos® Content Manager, and contains the following database scripts for Oracle, IBM DB2®, and Microsoft™ SQL Server:
  • acer_indexes_dbname.sql
  • acer_scripts_dbname.sql
  • acer_tables_dbname.sql

Procedure

Complete the following steps to run the database scripts:
  1. Run the following script against the Campaign database:

    acer_indexes_dbname.sql

    Make sure that you allow sufficient time for the script to complete. The time that the script requires to complete depends on the amount of data that is stored in the eMessage system tables. For more information, see For eMessage reports, installing stored procedures for delta processing

  2. Run the following script against the Campaign database:

    acer_tables_dbname.sql

    This script creates the delta processing staging tables in the eMessage system schema.

  3. Run the following script against the Campaign database:

    acer_scripts_dbname.sql

    This script creates the stored procedures that you must configure after you install reports for eMessage.

    When you use the script, if your database is DB2, change the termination character from ; (semicolon) to ! (exclamation point).

What to do next

Note: You must configure the stored procedures to run on a regular basis to populate the staging tables. You cannot see data in the reports until you run the stored procedures for eMessage reports.

For more information about running and scheduling the stored procedures, see Running and scheduling stored procedures for eMessage.